CODEWITHSUNDEEP
phpmysqlsql
user3201500
user3201500

Reputation: 1618

Run 2 update query as one in MySQL

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.

Upvotes: 1

Views: 757

Answers (2)

spencer7593
spencer7593

Reputation: 108510

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}

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271161

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}';

Upvotes: 1

Related Questions