MySQL - UPDATE multiple rows with different values in one query

Question

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

User · Accepted Answer

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

User · Answer

UPDATE Table1 SET col1  col2 FROM  SELECT col2  col3 FROM Table2  as newTbl WHERE col4  col3   Here col4  amp  col1 are in Table1  col2  amp  col3 are in Table2  I Am trying to update each col1 where col4   col3 different value for each row

User · Answer

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

User · Answer

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

User · Answer

I did it this way    lt update id  updateSettings  parameterType  PushSettings  gt       lt foreach collection  settings  item  setting  gt          UPDATE push setting SET status     setting status          WHERE type     setting type  AND user id     userId        lt  foreach gt   lt  update gt    where PushSettings is  public class PushSettings        private List lt PushSetting gt  settings      private String userId      it works fine

User · Answer

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

User · Answer

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

[mysql] MySQL - UPDATE multiple rows with different values in one query

Examples related to mysql

Examples related to sql

Examples related to sql-update