Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2006
    Location
    Leeds, UK
    Posts
    104

    Unanswered: stuck on a simple query!

    Hi

    This would be easier if i show a table of data then try to explain what i need to do!

    id - fac_id

    1234 - 1
    1234 - 2
    2345 - 1
    2346 - 1
    2347 - 1
    2347 - 3

    Basically i need to change all fac_id which = 1 and change them to fac_id = 2, this is simple enough:

    UPDATE SIC SET SIC_id = 2 WHERE SIC_id = 1

    How ever, i need an exception where the 'id' is the same so i don't get duplicate entries.

    So from the table above i need to change 2347 fac_id = 1 to fac_id = 2. I would NOT how ever change 1234 fac_id = 1 to 1234 fac_id =2 because there is already an id number with the same fac_id.

    Hope this errrr makes sense!!

    thanks
    Last edited by mattock; 02-19-07 at 13:34.

  2. #2
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    You just need to filter out all id's where there is already a 2?

    update sic
    set fac_id=2 where fac_id=1
    and [id] not in (select [id] from sic where fac_id=2)
    Inspiration Through Fermentation

  3. #3
    Join Date
    Aug 2006
    Location
    Leeds, UK
    Posts
    104
    thanks for that, worked a treat - easy when you know how!

Posting Permissions

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