CODEWITHSUNDEEP
mysqlinsert
tink01
tink01

Reputation: 347

MySQL Problem with inserting a row with certain conditions

I have a problem inserting a row in a MySQL table when the table is completely empty. I use this query :

INSERT IGNORE INTO test (id, amount) 
SELECT 6, 50 FROM test WHERE NOT EXISTS 
(SELECT 1 FROM test WHERE amount >= 50 AND id = 6) LIMIT 1

It works fine when there is at least one entry in the table, whatever the data in the columns are. It doesn't work if the table is completely empty.

Basically, I want to insert a row if a row with the same ID and an amount equals or higher doesn't exists.

I tried with a COUNT also, still the same problem. Is there another way of doing this?

Upvotes: 0

Views: 218

Answers (1)

Fosco
Fosco

Reputation: 38506

I think the only thing wrong with this is on line two, remove FROM test.. You can't select 6, 50 from test.. 6 and 50 are not columns in test, and test has no records. Try it like this:

INSERT IGNORE INTO test (id, amount) 
SELECT * from (select 6, 50) as a 
WHERE NOT EXISTS (SELECT 1 FROM test 
                  WHERE amount >= 50 AND id = 6)

Upvotes: 1

Related Questions