Update multiple query in mysql

I am trying to understand how to UPDATE multiple rows with different values and I just don't get it. The solution is everywhere but to me it looks difficult to understand.

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

I read an example, but I really don't understand 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 (??) ;

I'm not entirely clear how to do the query if there are multiple condition in the WHERE and in the IF condition..any ideas?

Update multiple query in mysql

SuperStormer

4,7495 gold badges20 silver badges32 bronze badges

asked Sep 4, 2014 at 20:48

Update multiple query in mysql

fran.sand66fran.sand66

10.2k19 gold badges57 silver badges100 bronze badges

1

You can do it this way:

UPDATE table_users
    SET cod_user = (case when user_role = 'student' then '622057'
                         when user_role = 'assistant' then '2913659'
                         when user_role = 'admin' then '6160230'
                    end),
        date = '12082014'
    WHERE user_role in ('student', 'assistant', 'admin') AND
          cod_office = '17389551';

I don't understand your date format. Dates should be stored in the database using native date and time types.

answered Sep 4, 2014 at 20:53

Gordon LinoffGordon Linoff

1.2m53 gold badges596 silver badges739 bronze badges

15

MySQL allows a more readable way to combine multiple updates into a single query. This seems to better fit the scenario you describe, is much easier to read, and avoids those difficult-to-untangle multiple conditions.

INSERT INTO table_users (cod_user, date, user_rol, cod_office)
VALUES
('622057', '12082014', 'student', '17389551'),
('2913659', '12082014', 'assistant','17389551'),
('6160230', '12082014', 'admin', '17389551')
ON DUPLICATE KEY UPDATE
 cod_user=VALUES(cod_user), date=VALUES(date)

This assumes that the user_rol, cod_office combination is a primary key. If only one of these is the primary key, then add the other field to the UPDATE list. If neither of them is a primary key (that seems unlikely) then this approach will always create new records - probably not what is wanted.

However, this approach makes prepared statements easier to build and more concise.

John

11.9k11 gold badges90 silver badges156 bronze badges

answered Jan 19, 2016 at 0:13

18

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

answered Sep 4, 2014 at 20:54

Update multiple query in mysql

Hart COHart CO

33.3k5 gold badges44 silver badges60 bronze badges

1

UPDATE table_name
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';

Saghachi

7199 silver badges16 bronze badges

answered Dec 11, 2016 at 7:43

Update multiple query in mysql

Akshay BhanAkshay Bhan

1952 silver badges12 bronze badges

To Extend on @Trevedhek answer,

In case the update has to be done with non-unique keys, 4 queries will be need

NOTE: This is not transaction-safe

This can be done using a temp table.

Step 1: Create a temp table keys and the columns you want to update

CREATE TEMPORARY TABLE  temp_table_users
(
    cod_user varchar(50)
    , date varchar(50)
    , user_rol varchar(50)
    ,  cod_office varchar(50)
) ENGINE=MEMORY

Step 2: Insert the values into the temp table

Step 3: Update the original table

UPDATE table_users t1
JOIN temp_table_users tt1 using(user_rol,cod_office)
SET 
t1.cod_office = tt1.cod_office
t1.date = tt1.date

Step 4: Drop the temp table

answered Mar 30, 2020 at 11:45

Update multiple query in mysql

SabSab

4705 silver badges16 bronze badges

In php, you use multi_query method of mysqli instance.

$sql = "SELECT COUNT(*) AS _num FROM test;
        INSERT INTO test(id) VALUES (1); 
        SELECT COUNT(*) AS _num FROM test; ";

$mysqli->multi_query($sql);

comparing result to transaction, insert, case methods in update 30,000 raw.

Transaction: 5.5194580554962
Insert: 0.20669293403625
Case: 16.474853992462
Multi: 0.0412278175354

As you can see, multiple statements query is more efficient than the highest answer.

Just in case if you get error message like this:

PHP Warning:  Error while sending SET_OPTION packet

You may need to increase the max_allowed_packet in mysql config file.

answered Dec 19, 2021 at 7:50

SaghachiSaghachi

7199 silver badges16 bronze badges

UPDATE Table1 SET col1= col2 FROM (SELECT col2, col3 FROM Table2) as newTbl WHERE col4= col3

Here col4 & col1 are in Table1. col2 & col3 are in Table2
I Am trying to update each col1 where col4 = col3 different value for each row

Update multiple query in mysql

answered Apr 23, 2020 at 4:25

Update multiple query in mysql

ankit giriankit giri

3083 silver badges9 bronze badges

I did it this way:


    
        UPDATE push_setting SET status = #{setting.status}
        WHERE type = #{setting.type} AND user_id = #{userId};
    

where PushSettings is

public class PushSettings {

    private List settings;
    private String userId;
}

it works fine

answered Apr 16, 2020 at 13:30

Update multiple query in mysql

ru51anru51an

1151 gold badge1 silver badge10 bronze badges

1

How UPDATE multiple columns with different values in SQL?

To update multiple columns use the SET clause to specify additional columns. Just like with the single columns you specify a column and its new value, then another set of column and values. In this case each column is separated with a column.

Can we UPDATE multiple rows in a single UPDATE statement?

Column values on multiple rows can be updated in a single UPDATE statement if the condition specified in WHERE clause matches multiple rows. In this case, the SET clause will be applied to all the matched rows.

How do you UPDATE multiple statements in SQL?

The UPDATE statement in SQL is used to update the data of an existing table in database. We can update single columns as well as multiple columns using UPDATE statement as per our requirement. UPDATE table_name SET column1 = value1, column2 = value2,...