Run 2 update query as one in MySQL

Question

I have 2 update queries that I want to combine into one query. Is there a way to do this?

Query 1:

update user_meta set
    meta_value = case meta_key
    when 'mobile' then '{$mobile}'
    when 'interest' then '{$interest}'
    when 'occupation' then '$occupation'
    when 'address' then '{$address}'
    when 'city' then '{$city}'
    when 'country' then '{$country}'
    when 'about' then '{$about}'
    when 'website' then '{$website}'
    else meta_value
    end
    where userid = {$id}

The other query:

update user set fullname='{$fullname}' where userid={$id}

Both of those queries are performed at the same time in same function, but have different tables. Also, this way I have to run two update queries.

And what if I want to put this when statements into a loop? If there are 100 values to update, it would be very hard.

You can 1. write a stored procedure inside the mysql server (which does whatever updates are required) or 2. use transactions, then performing multiple update statements is as safe as a single query... — arkascha, Jan 16 '14 at 16:14

score 1 · Answer 1 · answered Jan 16 '14 at 16:23

1

MySQL supports updating multiple tables in a single update:

update user_meta um join
       `user` u
       on um.userid = u.userid and
          u.userid = {$id}
    set um.meta_value = (case um.meta_key
                              when 'mobile' then '{$mobile}'
                              when 'interest' then '{$interest}'
                              when 'occupation' then '$occupation'
                              when 'address' then '{$address}'
                              when 'city' then '{$city}'
                              when 'country' then '{$country}'
                              when 'about' then '{$about}'
                              when 'website' then '{$website}'
                              else meta_value
                         end),
        u.fullname = '{$fullname}';

answered Jan 16 '14 at 16:23

Gordon Linoff

1,242,037
58
646
786

Thank you so much it worked very well and explanation is good. Can you please look at my another query? http://stackoverflow.com/questions/21157511/select-query-in-two-tables-in-mysql. I got the answer but i need more appropriate answer as everything i have new value added in meta_key i need to update the query as well. I dont want to do that.Please check if you can help then it will be great. – user3201500 Jan 16 '14 at 16:36
@user3201500 . . . I'm curious why you unselected my answer. Is there any particular reason? – Gordon Linoff Jan 16 '14 at 16:37
No its not like that. Your answer is just similar to another friend answer. But the only reason is he replied first. I am sorry. May i have your skype ID? If you wont mind. – user3201500 Jan 16 '14 at 16:49
@user3201500 . . . He didn't reply first. You can see the exact time of the reply by placing the mouse over the "answered" time button. The other answer was at 16:25:57, this answer was about 2.5 minutes earlier at 16:23:34. You are the OP and can choose to accept whichever answer you like, but you accepted mine first with praise in a comment and then unaccepted it -- which is why I asked. – Gordon Linoff Jan 16 '14 at 16:56

score 1 · Accepted Answer · answered Jan 16 '14 at 16:25

It's possible to update multiple tables in a single statement.

For example:

UPDATE user u
  JOIN user_meta m
    ON m.userid = u.userid
   SET m.meta_value = CASE m.meta_key
                      WHEN 'mobile'     THEN '{$mobile}'
                      WHEN 'interest'   THEN '{$interest}'
                      WHEN 'occupation' THEN '{$occupation}'
                      WHEN 'address'    THEN '{$address}'
                      WHEN 'city'       THEN '{$city}'
                      WHEN 'country'    THEN '{$country}'
                      WHEN 'about'      THEN '{$about}'
                      WHEN 'website'    THEN '{$website}'
                      ELSE m.meta_value
                      END
       , u.fullname = '{$fullname}'
   WHERE u.userid = {$id}

Is there any way i can make `when 'mobile' THEN '{$mobile}'` in a loop or some format i am using this in PHP. I want to use that multiple times and it may happen that user_meta value my increase with 100 more values. — user3201500, Jan 16 '14 at 16:30

Run 2 update query as one in MySQL

2 Answers2