234

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?

Improve this question
1

9 Answers 9

Reset to default
315

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

16 Comments

how I can do so that the update is executed, if the record already exists
@franvergara66 . . . I don't understand your comment. updates only affect records that already exist.
@franvergara66 . . . You may have a different problem. If cod_user is a primary key and the values are being shuffled around, then multiple updates is probably the best route.
yeah, i see, the real problem is that there are several assistants, then when trying to do an update, the integrity of the primary key is violated. Thank you very much for your time.
Be aware, this will set user_role to NULL for any record that is returned that doesn't match one of the three cases. Consider adding an ELSE before the end like this ELSE cod_user so that you set the field to itself if it doesn't match the WHEN clause.
|
194

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.

Improve this answer

19 Comments

Thank you! This is what I was searching for a long time, the cleanest looking way, I couldn't figure out this syntax, specifically cod_user=VALUES(cod_user), ..., even from official MySQL 5.6 docs
Note: This will add new rows if the key does not exist in the table resulting in unwanted records.
Tricky to use a never inserting IODKU, yet very elegant.
And note that this approach require primary key is set for the table.
This doesn't work if you omit any columns that can't be null, since sql still tries to create new record before actually resorting to update.
|
22 
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';
Improve this answer

Comments

16

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';
Improve this answer

1 Comment

You made a typo at the end of the CASE statement: you have 2 commas next to each other.
8

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

Improve this answer

1 Comment

It's disgusting that it's the only solution for tables with non-default fields like DATETIME
2

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.

Improve this answer

1 Comment

Which is normally disabled and is a security risk in case you did not run enough sanity checks on your code.
2

If your table has a unique key on two columns user_rol and cod_office, INSERT INTO ... ON DUPLICATE KEY UPDATE ... can be a solution as bellow but it uses gap lock (https://dev.mysql.com/doc/refman/8.3/en/innodb-locking.html#innodb-gap-locks) so there is a high chance to have deadlock if multiple statements are executed in parallel.

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)

In MySQL 5.x, you can use CASE WHEN THEN like this

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

In MySQL 8.x, you can join your table with VALUES (https://dev.mysql.com/doc/refman/8.0/en/values.html). I prefer this solution because the syntax is obvious, shorter and of-course easier for maintenance.

UPDATE table_users
SET date = '12082014', cod_user = temp_data.column_1
FROM (VALUES
  ROW('student', '622057'),
  ROW('assistant', '2913659'),
  ROW('admin', '6160230'),
) as temp_data
WHERE user_rol = temp_data.column_0 AND cod_office = '17389551';
Improve this answer

1 Comment

I'm unable to get the last query to work with MySQL 8.0.41. It seems that FROM is not a valid keyword that you can use with UPDATE. Relevant Docs: dev.mysql.com/doc/refman/8.0/en/update.html
1 
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

Improve this answer

Comments

-9

I did it this way:

<update id="updateSettings" parameterType="PushSettings">
    <foreach collection="settings" item="setting">
        UPDATE push_setting SET status = #{setting.status}
        WHERE type = #{setting.type} AND user_id = #{userId};
    </foreach>
</update>

where PushSettings is

public class PushSettings {

    private List<PushSetting> settings;
    private String userId;
}

it works fine

Improve this answer

1 Comment

author wants 1 query, it is clear he can do it with foreach, which will make several queries

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.