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.
-
I think, you'll have to create a stored procedure to solve this – user4035 May 26 '13 at 14:56
3 Answers
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.
![](../../users/profiles/1144035.webp)
- 1,122,135
- 50
- 484
- 624
-
That's a great and well working solution! Thank you for your help! – Anton Gildebrand May 26 '13 at 15:04
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.
Please see this How to 'insert if not exists' in MySQL?
![](../../users/profiles/-1.webp)
- 1
- 1
![](../../users/profiles/2422457.webp)
- 1,750
- 1
- 14
- 23
-
Yes, there is, as other answers have shown. Look at the IF() function in MySQL. – Connor Gurney May 26 '13 at 15:02