Insert where not exists...

bla4free · #1 (**permalink**) 04-06-09, 12:19

I'm trying to write a query. Here's what my table layout looks like:

Code:

TOCID    PROP_ID     STR_VAL
1        1           Yes
1        2           No
2        1           Yes
2        2           No
3        2           Yes
4        1           Yes
4        2           No

Basically, I need to insert a row with a prop_id = 1 for each tocid that does not have one. I have this, but it doesn't work. I don't know if I should be using NOT EXISTS or not.

Code:

INSERT INTO propval (tocid, prop_id, pos, str_val)
VALUES (tocid, 1, 0, 'No')
WHERE NOT EXISTS (SELECT * FROM propval WHERE prop_id = 1)

Thanks!

shammat · #2 (**permalink**) 04-06-09, 13:42

Close

this should work:

Code:

INSERT INTO propval (tocid, prop_id, pos, str_val)
SELECT 42, 1, 0, 'No'
WHERE NOT EXISTS (SELECT * FROM propval WHERE prop_id = 1)

Note that I replace tocid in the SELECT with a constant, as you cannot have a column there if you are not "really" selecting something

bla4free · #3 (**permalink**) 04-06-09, 14:03

Thanks! That worked. I can't believe I was missing a SELECT. I knew it would be something easy.

Internet Services

Web Development

Digital Marketing

Consumer Tech