thanksd thanksd - 3 months ago 6
MySQL Question

MySQL For each row with a column value A, add a new row with a column value B, if one does not already exist

I have a table with columns

id
,
foo_id
, and
bar_id
.

I want to go through the table and for each distinct
foo_id
, if there is a row for that
foo_id
with a
bar_id
equal to 2, I want to add a new row with that
foo_id
and with a
bar_id
equal to 3, unless a row with that
foo_id
and
bar_id
of 3 already exists.

For example:

Before:

id | foo_id | bar_id
--------------------
1 | 1 | 1
2 | 1 | 2
3 | 2 | 2
4 | 2 | 3
5 | 3 | 2
6 | 4 | 1


After:

id | foo_id | bar_id
--------------------
1 | 1 | 1
2 | 1 | 2
3 | 2 | 2
4 | 2 | 3
5 | 3 | 2
6 | 4 | 1
7 | 1 | 3
8 | 3 | 3


I've tried this query, but it's taking a really long time (>20 min) and I don't know if it works:

INSERT INTO mytable (bar_id, foo_id)
SELECT 3, (@var := foo_id) AS foo_id FROM mytable
WHERE bar_id=2 AND NOT EXISTS (
SELECT 1 FROM mytable
WHERE bar_id=3 AND foo_id=@var
) LIMIT 1;


Does anyone know how to do what I'm asking?

Answer

Try this:

 INSERT INTO mytable (bar_id, foo_id)
 SELECT 3, foo_id
 FROM mytable
 WHERE bar_id=2 AND foo_id NOT IN (
     SELECT foo_id FROM mytable WHERE bar_id=3 
 );
Comments