MySQL - UPDATE multiple rows with different values in one query

0 votes

Hello,

I wanted to know how to UPDATE multiple rows with different values and I just don't get it. 

For instance, three updates into 1 query:

UPDATE table_users
SET cod_user = '622057'
    , date = '12082014'
WHERE user_rol = 'student'
    AND cod_office = '17389551'; 

UPDATE table_users
SET cod_user = '2913659'
    , date = '12082014'
WHERE user_rol = 'assistant'
    AND cod_office = '17389551'; 

UPDATE table_users
SET cod_user = '6160230'
    , date = '12082014'
WHERE user_rol = 'admin'
    AND cod_office = '17389551'; 

 How to make the query. i.e:

UPDATE table_to_update
SET cod_user= IF(cod_office = '17389551','622057','2913659','6160230')
    ,date = IF(cod_office = '17389551','12082014')
WHERE ?? IN (??) ;

 How to execute the query if there are multiple condition in the WHERE and in the IF condition.Any ideas?

Jun 1 in PHP by kartik
• 15,440 points
129 views

1 answer to this question.

0 votes

Hello @kartik

You can use a CASE statement to handle multiple if/then scenarios:

UPDATE table_to_update 
SET  cod_user= CASE WHEN user_rol = 'student' THEN '622057'
                   WHEN user_rol = 'assistant' THEN '2913659'
                   WHEN user_rol = 'admin' THEN '6160230'
               END
    ,date = '12082014'
WHERE user_rol IN ('student','assistant','admin')
  AND cod_office = '17389551';

Hope this is helpfull!

answered Jun 1 by Niroj
• 32,200 points

Related Questions In PHP

0 votes
1 answer

How to log in to phpMyAdmin with WAMP?

Hello @kartik,  Try-Username: root Password: (No password set). Sometimes it doesn't ...READ MORE

answered Apr 9 in PHP by Niroj
• 32,200 points
96 views
0 votes
1 answer

How to order results with findBy() in Doctrine

Hii @kartik, Try out this code $ens = $em->getRepository('AcmeBinBundle:Marks')               ...READ MORE

answered Apr 20 in PHP by Niroj
• 32,200 points
51 views
0 votes
1 answer

Error:“Connection for controluser as defined in your configuration failed” with phpMyAdmin in XAMPP

Hii, I edited /etc/phpmyadmin/config.inc.php and changed the following ...READ MORE

answered Apr 20 in PHP by Niroj
• 32,200 points
179 views
0 votes
1 answer

How to convert from MySQL datetime to another format with PHP?

Hello, To convert a date retrieved from MySQL ...READ MORE

answered May 19 in PHP by Niroj
• 32,200 points
34 views
0 votes
1 answer

How to make anchor tag with routing using Laravel?

Hey @kartik, First you have to go to ...READ MORE

answered Mar 18 in Laravel by Niroj
• 32,200 points
810 views
0 votes
1 answer

What is redirection in Laravel?

Named route is used to give specific ...READ MORE

answered Mar 18 in Laravel by Niroj
• 32,200 points
82 views
0 votes
1 answer

How to install Laravel via composer?

Hello, This is simple you just need to ...READ MORE

answered Mar 23 in Laravel by Niroj
• 32,200 points
94 views
0 votes
1 answer

What are named routes in Laravel and How can specify route names for controller actions?

Hey @kartik, Named routing is another amazing feature of ...READ MORE

answered Mar 23 in Laravel by Niroj
• 32,200 points
434 views
0 votes
1 answer

Connection with MySQL server using PHP. How can we do that?

Hey @kartik, You have to provide MySQL hostname, ...READ MORE

answered Mar 27 in PHP by Niroj
• 32,200 points
48 views
0 votes
1 answer

How can I get ID of the last updated row in MySQL?

Hello @kartik, Suppose, item_id is an integer identity column in items table ...READ MORE

answered Apr 9 in PHP by Niroj
• 32,200 points
116 views