multiple update query mysql

Question

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

score 3 · Accepted Answer · 2011-10-17 10:07:46Z

3

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')

edited Oct 17, 2011 at 10:07

answered Oct 17, 2011 at 9:33

user330315

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

10 Comments

Gunjan Nigam Over a year ago

For records which don't come under any WHEN case are set to default. How do I stop that?

Gunjan Nigam Over a year ago

In that case what if I need to update records for two different StationIds like record 2 and record 3, then what should I do?

user330315 Over a year ago

You have to add the condition to the where and move the check for the station id back to the CASE statement. See my edit, you need to change the WHERE condition to reflect what you really want to update. My example might not be want you want.

Jan S Over a year ago

You could have a limiting clause for Ids in your case statement and a where clause to prevent edits to non specified Ids

user330315 Over a year ago

I'm sure you can adjust my example to support that requirement.

|

ravnur · Accepted Answer · 2011-10-17 09:36:40Z

2

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

answered Oct 17, 2011 at 9:36

ravnur

2,85223 silver badges28 bronze badges

1 Comment

Gunjan Nigam Over a year ago

Yaa I got that error removed now. The manual at CASE had CASE in the end. Now I have another problem. I have edited my question. Have a look

Jan S · Accepted Answer · 2011-10-17 10:29:03Z

2

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')

edited Oct 17, 2011 at 10:29

answered Oct 17, 2011 at 9:34

Jan S

1,83715 silver badges21 bronze badges

Comments

wns · Accepted Answer · 2011-10-17 09:32:04Z

0

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

mysql>CREATE INDEX index_name ON tableName(StationId);

edited Oct 17, 2011 at 9:32

answered Oct 17, 2011 at 9:22

wns

3,6263 gold badges46 silver badges59 bronze badges

1 Comment

Gunjan Nigam Over a year ago

Since StationId and ServerDate are combined primary key I don't think I need to index my StationId. I will always be searching using a combination of StationId and ServerDate. And I don't want to update in multiple steps. I want to update using one query only

Collectives™ on Stack Overflow

multiple update query mysql

4 Answers 4

10 Comments

1 Comment

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

10 Comments

1 Comment

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related