MySQL Problem with inserting a row with certain co

问题:

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?

回答1:

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)