Question 266744 in Mysql Question posted in
The official MySQL documentation can be found here.

How to update one column's multiple rows with multiple values in MySQL?

I have a data table like the name as data_table and sample data are like below

id name type temp_id
1 n1 t1
1 n1 t1
1 n1 t1
1 n1 t1
2 n1 t1
2 n1 t1
2 n1 t1
2 n1 t1
2 n1 t1

I would like to update , temp_id columns with specific unique values. Like below.

id name type temp_id
1 n1 t1 1001
1 n1 t1 1002
1 n1 t1 1003
1 n1 t1 1004
2 n1 t1 1005
2 n1 t1 1006
2 n1 t1 1007
2 n1 t1 1008
2 n1 t1 1009
Tags:

2

Answers


  1. 0

    Using CASE Expression:

    You can use a CASE expression to set different values for each row you want to update. Here’s an example:

    UPDATE your_table
    SET column_to_update = 
    CASE 
        WHEN some_condition1 THEN new_value1
        WHEN some_condition2 THEN new_value2
        WHEN some_condition3 THEN new_value3
        -- Add more conditions and new values as needed
        ELSE column_to_update
    END;
    Login or Signup to reply.
  2. 0 
    CREATE TABLE test
SELECT 1 id, 'n1' name, 't1' type, CAST(NULL AS UNSIGNED) temp_id UNION ALL
SELECT 1, 'n1', 't1', NULL UNION ALL
SELECT 1, 'n1', 't1', NULL UNION ALL
SELECT 1, 'n1', 't1', NULL UNION ALL
SELECT 2, 'n1', 't1', NULL UNION ALL
SELECT 2, 'n1', 't1', NULL UNION ALL
SELECT 2, 'n1', 't1', NULL UNION ALL
SELECT 2, 'n1', 't1', NULL UNION ALL
SELECT 2, 'n1', 't1', NULL ;
SELECT * FROM test;

    Records: 9  Duplicates: 0  Warnings: 0
    id name type temp_id
    1 n1 t1 null
    1 n1 t1 null
    1 n1 t1 null
    1 n1 t1 null
    2 n1 t1 null
    2 n1 t1 null
    2 n1 t1 null
    2 n1 t1 null
    2 n1 t1 null 
    SET @start_from := 1000;
UPDATE test
SET temp_id = (@start_from := @start_from + 1);

    Rows matched: 9  Changed: 9  Warnings: 1

    SELECT * FROM test;
    id name type temp_id
    1 n1 t1 1001
    1 n1 t1 1002
    1 n1 t1 1003
    1 n1 t1 1004
    2 n1 t1 1005
    2 n1 t1 1006
    2 n1 t1 1007
    2 n1 t1 1008
    2 n1 t1 1009

    fiddle

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search