Update Field WIth Multiple Values Based on Multiple Options

Question

I have a table "Master" with fields called "DFM" and "Target". I ideally need 1 UPDATE query that will populate the "Target" field based on the value of DFM as below:

DFM Target

50001   85
50009   255
50011   233
50012   290
50062   183
50063   150
50064   159.5
50142   187
50143   174
50179   284.25
50180   195.75
50286   157.25
50287   231.25

For example if the DFM value is 50142 it should UPDATE the field for that row with 187.

So can this be done with 1 query, or do I need 13?

I only know the long winded way ie

UPDATE Master, SET Target = 85 WHERE DFM = 50001

I don't really want 13 queries though.

There seems to be some information missing. Your Master table already has the values for Target you want to update them to. As you looking to update the Target field on some other table based on the values on Master or the values on Master based on some other lookup table? — SunKnight0
– SunKnight0, Commented Jan 6, 2017 at 15:22
No, sorry if it isn't clear, the DFM is populated but not Target. The info I gave above was merely the data I wanted populating just so you can see what it looks like. — davidjwest
– davidjwest, Commented Jan 6, 2017 at 15:25
Then the solution given below (with my correction) should work. If target is not populated at all or if all your potential values for dfm have a replacement value inside the switch you can also skip the where part of it. — SunKnight0
– SunKnight0, Commented Jan 6, 2017 at 15:30

Gordon Linoff · Accepted Answer · 2017-01-07 02:55:51Z

2

You can use a switch:

update master
    set target = switch(dfm = 50001, 85,
                        dfm = 50009, 255,
                        . . .
                       )
    where dfm in (50001, 50009, . . .);

edited Jan 7, 2017 at 2:55

answered Jan 6, 2017 at 15:19

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

SunKnight0 Over a year ago

That should be switch(dfm=50001, 85, dfm=50009, 255, ...)

Collectives™ on Stack Overflow

Update Field WIth Multiple Values Based on Multiple Options

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related