SQL Insert if NULL

Question

Is there a purely SQL way to do this?

I have a table apples: id, price, and another apple_info: apple_id, color

For each row in apples, I want to add a corresponding row in apple_info, if it does not already exist. Can't find any examples of this.

as each apple is inserted, or after the fact against the whole apple table? How is color value provided for the insert into apple_info? — hatchet - done with SOverflow, Aug 16 '11 at 23:09

score 3 · Accepted Answer · answered Aug 16 '11 at 23:08

3

Try this:

insert into apple_info (apple_id, color)
select a.id, 'some_color'
  from apples a
  left outer join apple_info ai
    on (a.id = ai.apple_id)
 where ai.apple_id is null;

You will insert a row on apple_info with non existent apple_id and a fixed value for color. I guess this is what you want.

answered Aug 16 '11 at 23:08

Pablo Santa Cruz

176,835
32
241
292

Thank you, this is exactly what I had in mind. `LEFT OUTER JOIN` threw me off, but turns out it's the same as `LEFT JOIN` in MySQL: http://stackoverflow.com/questions/2809594/basic-question-left-outer-join-vs-left-join – atp Aug 16 '11 at 23:19

score 1 · Answer 2 · answered Aug 16 '11 at 23:09

1

Use INSERT IGNORE:

INSERT IGNORE INTO apple_info(apple_id, color)
    SELECT id, 'green' FROM apples

answered Aug 16 '11 at 23:09

Abe Miessler

82,532
99
305
486

SQL Insert if NULL

2 Answers2