MySQL insert if rows exist in temporary table

Question

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.

Answer 1

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.

Answer 2

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.

Answer 3

Please see this How to 'insert if not exists' in MySQL?