CODEWITHSUNDEEP
mysql
Gunjan Nigam
Gunjan Nigam

Reputation: 1393

multiple update query mysql

I have a table in which primary key is combination of two columns. I want to update multiple rows depending upon my Primary keys

StationId   ServerDate          Status
1           2011-05-05 01:00:00 0
1           2011-05-06 01:00:00 1
2           2011-05-05 01:00:00 2

My update queries currently look like

Update data set status = 1 where StationId = '1' and  ServerDate = '2011-05-05 01:00:00'
Update data set status = 2 where StationId = '1' and  ServerDate = '2011-05-06 01:00:00'

I was thinking of using CASE statement but couldn't figure on how to use it when key is combination of two keys. This is the query I wrote using CASE. Its changing all my rows. If a current record doesn't come under any when condition like record 3, its changed to default value 0. I want that record to retain previous value.

UPDATE data set status = CASE 
   WHEN StationId = '1' and  ServerDate = '2011-05-05 01:00:00' THEN 1 
   WHEN StationId = '1' and  ServerDate = '2011-05-06 01:00:00' THEN 2 
END

Upvotes: 1

Views: 605

Answers (4)

Jan S
Jan S

Reputation: 1837

Your query should be: (EDITED)

UPDATE data set status = 
(
   CASE 
       WHEN StationId = '1' AND ServerDate = '2011-05-05 01:00:00' THEN 1 
       WHEN StationId = '2' AND ServerDate = '2011-05-06 01:00:00' THEN 2 
    END 
)
WHERE StationId IN ('1','2')

Upvotes: 2

user330315
user330315

Reputation:

This is the query I wrote using CASE but I got errors in query

Remove the "CASE" after the END keyword and it should be fine:

UPDATE data 
   SET status = 
     CASE 
       WHEN stationId = '1' and ServerDate = '2011-05-05 01:00:00' THEN 1 
       WHEN stationId = '1' and ServerDate = '2011-05-06 01:00:00' THEN 2 
       WHEN stationId = '2' and ServerDate = '2011-05-05 01:00:00' THEN 3
     END
WHERE stationId IN ('1', '2')
  AND ServerDate in ('2011-05-05 01:00:00', '2011-05-06 01:00:00')

Upvotes: 3

ravnur
ravnur

Reputation: 2852

You just need to remove your last "CASE"

UPDATE data set status = CASE 
   WHEN StationId = '1' and  ServerDate = '2011-05-05 01:00:00' THEN 1 
   WHEN StationId = '1' and  ServerDate = '2011-05-06 01:00:00' THEN 2 
END

Upvotes: 2

wonu wns
wonu wns

Reputation: 3608

you can make StationId your index for faster query when you handle multiple rows.

mysql>CREATE INDEX index_name ON tableName(StationId);

Upvotes: 0

Related Questions