0

Is what I'm asking in my title possible? I need to "OR" some sets in an update.

UPDATE prtmst
SET prtfam = '1'
WHERE prtnum IN ('111', '222', '333', '444')
AND prtfam = '1M'

That runs fine but I need it to be something like:

UPDATE prtmst
SET prtfam = '1'
WHERE prtnum IN ('111', '222', '333', '444')
AND prtfam = '1M'
 OR SET prtfam = '2'
WHERE prtnum IN ('111', '222', '333', '444')
AND prtfam = '2M'
 OR SET prtfam = '3'
WHERE prtnum IN ('111', '222', '333', '444')
AND prtfam = '3M'
 OR SET prtfam = '1M'
WHERE prtnum IN ('111', '222', '333', '444')
AND prtfam = '1'
 OR SET prtfam = '2M'
WHERE prtnum IN ('111', '222', '333', '444')
AND prtfam = '2'
 OR SET prtfam = '3M'
WHERE prtnum IN ('111', '222', '333', '444')
AND prtfam = '3'
user3642066
  • 55
  • 2
  • 9
  • 3
    You can use CASE and IN to perform your update. Examples here: http://stackoverflow.com/questions/5170864/update-with-case-and-in-oracle – rtbf Aug 21 '14 at 12:50

1 Answers1

2

please run some tests before trusting this answer. It should work, but since I don't know your schema, bad things might happen.

UPDATE prtmst
SET prtfam =
CASE
    WHEN prtnum IN ('XBR140006T', 'XCA02334', 'XCA02344')   
        AND prtfam = 'FERRERO1M' 
        THEN  'FERRERO1'
    WHEN prtnum IN ('XBR140006T', 'XCA02334', 'XCA02344')
        AND prtfam = 'FERRERO2M'
        THEN 'FERRERO2'
    WHEN prtnum IN ('XBR140006T', 'XCA02334', 'XCA02344')
        AND prtfam = 'FERRERO3M'
        THEN 'FERRERO3'
    WHEN prtnum IN ('XBR140006T', 'XCA02334', 'XCA02344')
        AND prtfam = 'FERRERO1'
        THEN 'FERRERO1M'
    WHEN prtnum IN ('XBR140006T', 'XCA02334', 'XCA02344')
        AND prtfam = 'FERRERO2'
        THEN 'FERRERO2M'
    WHEN prtnum IN ('XBR140006T', 'XCA02334', 'XCA02344')
        AND prtfam = 'FERRERO3'
        THEN  'FERRERO3M'
END
WHERE prtnum IN ('XBR140006T', 'XCA02334', 'XCA02344')
  AND prtfam IN ('FERRERO1M','FERRERO2M','FERRERO3M','FERRERO1','FERRERO2','FERRERO3')

man, that Ferrero guy is giving you hell.

EDIT: added a WHERE clause to avoid trying to update rows not contained in the CASE ... WHEN block.

ffflabs
  • 17,166
  • 5
  • 51
  • 77
  • I had something very similar but I got back "Update or delete statement must include a where clause". Any ideas? – user3642066 Aug 21 '14 at 13:02
  • well, if that CASE ... WHEN block does not contain the whole universe, then the query might fail. Let me add something – ffflabs Aug 21 '14 at 13:04
  • That is very well the case. I'm not a very big SQL guy (web developer) so I'm not 100% with the CASE structure/logic/how it compiles down. I get that there's other conditions that are obviously being missed/escaping the case thus resulting in no set. That makes sense. Is there a keyword I can use to "exit" the logic structure if a condition above isn't met? – user3642066 Aug 21 '14 at 13:05
  • Thank you, amenadiel! Works perfectly – user3642066 Aug 21 '14 at 13:09