Update query SQL to update various fields of table?

Question

This is my table:

id    delivery_mode    old_delivery_mode

123   email             sms
126   sms               sms
139   email            facebook
147   facebook          email
198    sms               sms
210   sms               email

I want to update my table field values with these values:

 sms=100
 email=200
 facebook=300

So that:

id    delivery_mode    old_delivery_mode

123   200               100
126   100               100
139   200               300
147   300               200
198   100               100
210   100               200

I want to do this in single sql query.

How can I do it?

Is this a question for MySQL or MSSQL? And what have you tried? What problems did you run into? — NickyvV, Aug 01 '14 at 06:57
I don't understand what "update my table field with sms=100" is supposed to mean. Do you want to replace the value `sms` with the value `100`? Do you want to replace update the column `old_delivery_mode` to something else when the id is 100? Do you want to update the `delivery_mode` column to `sms` when the id = 100? — , Aug 01 '14 at 07:06

score 2 · Answer 1 · answered Aug 01 '14 at 07:31

UPDATE  YOUR_TABLE_NAME
SET     delivery_mode =
        CASE
            WHEN delivery_mode = 'sms' THEN '100'
            WHEN delivery_mode = 'email' THEN '200'
            WHEN delivery_mode = 'facebook' THEN '300'
           WHEN delivery_mode THEN delivery_mode
        END,
     old_delivery_mode =
        CASE
            WHEN old_delivery_mode = 'sms' THEN  '100'
            WHEN old_delivery_mode = 'email' THEN '200'
            WHEN old_delivery_mode = 'facebook' THEN '300'
        WHEN old_delivery_mode THEN old_delivery_mode
        END

score 0 · Answer 2 · edited May 23 '17 at 11:59

0

Yes, that's possible - you can use INSERT ... ON DUPLICATE KEY UPDATE.

Using example:

INSERT INTO table (id,Col1,Col2) VALUES (1,1,1),(2,2,3),(3,9,3),(4,10,12)
ON DUPLICATE KEY UPDATE Col1=VALUES(Col1),Col2=VALUES(Col2);

I found it here

is this what you are looking for?

edited May 23 '17 at 11:59

Community

1
1

answered Aug 01 '14 at 07:02

mahesh247

199
3
8

score 0 · Answer 3 · answered Aug 01 '14 at 07:08

I am not sure if this is what you want

Update table1 set 
delivery_mode = case delivery_mode when 'sms' then '100' when 'email' then '200' when facebook then '300' end,
old_delivery_mode = case delivery_mode when 'sms' then '100' when 'email' then '200' when facebook then '300' end

score 0 · Answer 4 · answered Aug 01 '14 at 07:12

Is this you want?

update your_table 
set old_delivery_mode = delivery_mode,
    delivery_mode = 
      case delivery_mode 
        when 'sms' then '100'
        when 'email' then '200'
        when 'facebook' then '300'
      end
where delivery_mode in ('sms','email','facebook')

Tested under sqllite.

score 0 · Answer 5 · answered Aug 01 '14 at 07:13

The SQL equivalent would be using CASE, like so:

UPDATE myTable
SET delivery_mode = CASE delivery_mode 
      WHEN 'sms' THEN '100'
      WHEN 'email' THEN '200'
      WHEN 'facebook' THEN '300' 
      ELSE delivery_mode END -- ELSE to make sure other values aren't touched
   , old_delivery_mode = CASE old_delivery_mode 
      WHEN 'sms' THEN '100'
      WHEN 'email' THEN '200'
      WHEN 'facebook' THEN '300' 
      ELSE old_delivery_mode END
-- And WHERE to make sure only the applicable rows are updated
WHERE delivery_mode IN ('sms','email','facebook')
OR old_delivery_mode IN ('sms','email','facebook')

There are a few extra checks in there (commented) since we don't know what other values those columns might have, or how many rows from the whole table should be considered for this update.

Update query SQL to update various fields of table?

5 Answers5