CODEWITHSUNDEEP
sqloracle-database
user3642066
user3642066

Reputation: 65

ORACLE/SQL - UPDATE Statement Issue (Need to OR some sets)

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'

Upvotes: 0

Views: 54

Answers (1)

ffflabs
ffflabs

Reputation: 17501

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.

Upvotes: 2

Related Questions