CODEWITHSUNDEEP
mysqlsql
Alpay Toprak
Alpay Toprak

Reputation: 23

How can I update multiple rows with one column compare in sql?

I can update one column by comparing multiple columns but how can I update multiple columns with one query? Here is my working code ;

Update HMI.[dbo].Mak8 SET reg2= CASE 
WHEN Mak_Ip = '192.168.0.6'  THEN 200
WHEN Mak_Ip = '192.168.0.8'  THEN 210
ELSE reg2 END

But when I tried to update multiple columns, I could not do that ;

Update HMI.[dbo].Mak8 SET reg2, reg3, reg4 = CASE 
WHEN Mak_Ip = '192.168.0.6'  THEN 200, 250, 270
WHEN Mak_Ip = '192.168.0.8'  THEN 210, 270, 300
ELSE reg2, reg3, reg4 END

I got this error message: Msg 102, Level 15, State 1, Line 1 Incorrect syntax near ','. How can I use it and how can use it?

Upvotes: 0

Views: 68

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1270533

This is a nice opportunity to use a values statement:

UPDATE m
    SET reg2 = v.reg2,
        reg3 = v.reg3,
        reg4 = v.reg4
    FROM HMI.[dbo].Mak8 m JOIN
         (VALUES ('192.168.0.6', 200, 250, 270),
                 ('192.168.0.6', 210, 270, 300)
         ) v(Mak_Ip, reg2, reg3, reg4)
         ON m.Mak_Ip = v.Mak_Ip;

This makes it particularly simple to add new ips and to add new columns, if you need.

Upvotes: 0

Kazi Mohammad Ali Nur Romel
Kazi Mohammad Ali Nur Romel

Reputation: 15905

You can't do that. You need to set values to each column seperately :

Update HMI.[dbo].Mak8 SET reg2 = (CASE 
WHEN Mak_Ip = '192.168.0.6'  THEN 200
WHEN Mak_Ip = '192.168.0.8'  THEN 210
END),
reg3= (CASE 
WHEN Mak_Ip = '192.168.0.6'  THEN  250
WHEN Mak_Ip = '192.168.0.8'  THEN  270
END),
reg4= (CASE 
WHEN Mak_Ip = '192.168.0.6'  THEN  270
WHEN Mak_Ip = '192.168.0.8'  THEN  300
END)

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522191

You probably will need to use separate CASE expressions here:

UPDATE HMI.[dbo].Mak8
SET
    reg2 = CASE Mak_Ip WHEN '192.168.0.6' THEN 200
                       WHEN '192.168.0.8' THEN 210 END,
    reg3 = CASE Mak_Ip WHEN '192.168.0.6' THEN 250
                       WHEN '192.168.0.8' THEN 270 END,
    reg4 = CASE Mak_Ip WHEN '192.168.0.6' THEN 270
                       WHEN '192.168.0.8' THEN 300 END
WHERE
    Mak_Ip IN ('192.168.0.6', '192.168.0.8');

By the way [dbo] seriously looks like SQL Server syntax, but as it would have it, my answer would not really change much even if you were really using MySQL.

Upvotes: 1

Related Questions