Answer the question
In order to leave comments, you need to log in
INSERT and IF. How right?
There is a table table. Three fields: autoincrement, two, three.
You need to add a row with values in the fields two, three if it is not in this table.
I thought to use ignore, but the first field is autoincrement, and two, three have duplicate elements, so you can't make them PRIMARY or UNIQUE.
After 2-3 hours of trying, I came up with this code:
SELECT IF((SELECT three FROM table WHERE two='100' AND three='1') IS NULL, @res:=1, 0) RES;
INSERT INTO table (two, three) (SELECT '100','1' FROM table WHERE @res=1 LIMIT 1)
SELECT @res:=autoincrement FROM table WHERE two=46 AND three=24;
INSERT IGNORE INTO table (autoincrement,two, three) VALUES (@res, 46, 24)
Answer the question
In order to leave comments, you need to log in
And who prevents to make a composite unique key UNIQUE KEY `two_three` (`two`, `three`)
PS. Well, or something like this:
INSERT INTO `table` (`two`, `three`)
SELECT `t1`.`two`, `t1`.`three`
FROM (
SELECT :two AS `two`, :three AS `three`
) AS `t1`
LEFT JOIN `table` AS `t2` USING (`two`, `three`)
WHERE `t2`.`two` IS NULL;
and you can't assign default values to fields two and three? Or is there some sort of dependency?
Didn't find what you were looking for?
Ask your questionAsk a Question
731 491 924 answers to any question