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, 2020 in PHP by kartik
• 37,500 points
23,746 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, 2020 by Niroj
• 82,740 points

Related Questions In PHP

0 votes
0 answers

Get last executed query in MySQL with PHP/CodeIgniter

In my_model.php, I have: $query1 = ( ...something... ); $query2 ...READ MORE

Jul 22 in PHP by Kithuzzz
• 4,080 points
16 views
0 votes
2 answers

Define a SQL query? What is the difference between SELECT and UPDATE Query? How do you use SQL in SAS?

HI.. SQL is Structured Query Language, which is ...READ MORE

answered Aug 8, 2020 in PHP by anonymous
4,734 views
0 votes
1 answer

How to concatenate text from multiple rows into a single text string in SQL server?

Hello @kartik, Use COALESCE: DECLARE @Names VARCHAR(8000) SELECT @Names = ...READ MORE

answered Jul 21, 2020 in PHP by Niroj
• 82,740 points
1,997 views
0 votes
1 answer

How can I do an UPDATE statement with JOIN in SQL Server?

Hello @kartik, This should work in SQL Server: update ...READ MORE

answered Jul 21, 2020 in PHP by Niroj
• 82,740 points
269 views
+1 vote
1 answer

How to make anchor tag with routing using Laravel?

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

answered Mar 18, 2020 in Laravel by Niroj
• 82,740 points
14,628 views
0 votes
1 answer

What is redirection in Laravel?

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

answered Mar 18, 2020 in Laravel by Niroj
• 82,740 points
1,652 views
0 votes
1 answer

How to install Laravel via composer?

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

answered Mar 23, 2020 in Laravel by Niroj
• 82,740 points
1,368 views
+1 vote
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, 2020 in Laravel by Niroj
• 82,740 points
19,823 views
0 votes
1 answer

How to Insert multiple rows in a single SQL query?

Hello @kartik, In SQL Server you can insert ...READ MORE

answered Jul 21, 2020 in PHP by Niroj
• 82,740 points
3,080 views
0 votes
1 answer

Error:Unknown column in 'field list' error on MySQL Update query

Hello @kartik, Enclose any string to be passed ...READ MORE

answered Aug 20, 2020 in PHP by Niroj
• 82,740 points
10,825 views
webinar REGISTER FOR FREE WEBINAR X
Send OTP
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP