MySQL在某些条件下插入行的问题 [英] MySQL Problem with inserting a row with certain conditions

问题描述

当表完全为空时，我在MySQL表中插入一行时遇到问题. 我使用此查询:

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.

基本上，如果不存在具有相同ID且等于或大于等于数量的行，我想插入一行.

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

我也尝试过使用COUNT，仍然是同样的问题.还有另一种方法吗?

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

推荐答案

我认为唯一的问题是在第二行，删除FROM test ..您不能select 6, 50 from test .. 6和50不是测试中的列，并且测试没有记录.像这样尝试:

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)

这篇关于MySQL在某些条件下插入行的问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！