Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2005
    Posts
    165

    Insert where not exists...

    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!

  2. #2
    Join Date
    Nov 2003
    Posts
    2,821
    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

  3. #3
    Join Date
    Jan 2005
    Posts
    165
    Thanks! That worked. I can't believe I was missing a SELECT. I knew it would be something easy.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •