CODEWITHSUNDEEP
sqlinsertmissing-data
Matthias Ronge
Matthias Ronge

Reputation: 10112

In SQL, how can I add a row only if there isn’t any yet to a table?

Example: I have four objects, where 1 has three foobars (two "FOO" and one "BAR"), 2 has a "FOO" foobar (no "BAR" foobar here #1), 3 has a "BAR" and a "Good morning, John! :)" foobar, and 4 does not have any (no "BAR" foobar here #2). Notice the type column is string, not necessarily unique, and may contain arbitrary crab es well.

Table 'object':

| id | ...
+----+----
| 1  | ...
| 2  | ...
| 3  | ...
| 4  | ...

Table 'foobar':

| id | object_id | type                     | ...
+----+-----------+--------------------------+----
| 1  | 1         | "FOO"                    | ...
| 2  | 1         | "FOO"                    | ...
| 3  | 1         | "BAR"                    | ...
| 4  | 2         | "FOO"                    | ...
| 5  | 3         | "BAR"                    | ...
| 6  | 3         | "Good morning, John! :)" | ...

What I want to do is a command: For each object.id, if there is no foobar with object_id = object.id and foobar.type = 'BAR', add to foobar (next id, object.id, 'BAR', …). After the command, the following should have been added to the foobar table:

| id | object_id | type  | ...
+----+-----------+-------+----
| 7  | 2         | "BAR" | ...  // adds missing #1
| 8  | 4         | "BAR" | ...  // adds missing #2

Techniques I have read about to insert missing columns, such as INSERT IGNORE and INSERT ... ON DUPLICATE KEY UPDATE will not work here if I understand it correctly, mainly because 'type' cannot be unique here (due to data restrictions).

So basically what I need to do is a join on a non-existing row in order to add it, or so. I have no idea.

I am using MySQL dialect, if that helps something.

Upvotes: 0

Views: 43

Answers (2)

LukStorms
LukStorms

Reputation: 29647

So just find the objects that don't have a 'BAR' in foobar.
And then insert those into foobar.

If foobar.id is a primary key with AUTO_INCREMENT, then you don't need to insert an id.

INSERT INTO foobar (object_id, type)
SELECT o.id, 'BAR' as Type
FROM object o
LEFT JOIN foobar f ON (f.object_id = o.id AND f.type = 'BAR')
WHERE f.id IS NULL

The SQL trick that's used here is a rather old one.

When you LEFT JOIN a table.
For example: SELECT * FROM a LEFT JOIN b ON b.a_id = a.id
Then it will return all rows from "a", even if the row couldn't be linked to "b".
Then when the matching record in "b" is missing, the b.id would be NULL in the resultset.
So filtering on b.id IS NULL returns the "a" that don't have a matching "b".

Upvotes: 2

JohnHC
JohnHC

Reputation: 11195

Use a not exists:

insert into mytable (object_id, type)
select object_id, 'BAR'
from mytable t1
where type = 'FOO'
and not exists 
(
select 1 
from mytable t2 
where t1.object_id = t2.object_id 
and type = 'BAR'
)

Upvotes: 1

Related Questions