I want to insert a row into my table if there are 1 or more records in my temporary table temp. Usually it's the other way around where i'd want to update if the row exists, and insert otherwise. So how would i solve this? It doesn't seem like there is a if exists statement i could use, and a quick search shows me that INSERT INTO doesn't support the where-clause.
Asked
Active
Viewed 1,054 times
1
-
I think, you'll have to create a stored procedure to solve this – user4035 May 26 '13 at 14:56
2 Answers
4
insert into definitely does support a where clause. You just have to use the insert into . . . select form of it:
insert into mytable(<whatever>)
select <whatever>
from (select count(*) as cnt from temp) c
where c.cnt > 0;
Okay, technically the where clause is part of the select, but it does what you want.
MySQL does not support where clause without a from clause. This solves that problem by using the from clause to calculate the count in the temp table. If the count is greater than 0 (or whatever number you want), then it inserts the row.
Gordon Linoff
- 1,242,037
- 58
- 646
- 786
-
That's a great and well working solution! Thank you for your help! – Anton Gildebrand May 26 '13 at 15:04
0
Like @Gordon Linoff's answer, the following is based on the same idea of using INSERT...SELECT instead of INSERT...VALUES, because the former allows one to throw in a WHERE condition. However, I would approach the problem of getting around the limitation of WHERE without FROM differently:
INSERT INTO target_table (list_of_columns)
SELECT *
FROM (SELECT list_of_values) s
WHERE EXISTS (SELECT * FROM temp_table)
;If the temporary table may hold many rows, this might perform better than Gordon's suggestion, because EXISTS (SELECT * ...) doesn't need to scan the entire table when there's at least one row that matches the subquery, while SELECT COUNT(*) ... would need to count all the rows every time.
If there can't be many rows in the temporary table, the advantage, if any, would likely be unnoticeable.
