Mysql Update mutiple row of datas

Question

I would like to know how to efficiently update multiple rows of data with the UPDATE statement. I know I can insert multiple records like this one below.

INSERT INTO example
  (example_id, name, value, other_value)
VALUES
  (100, 'Name 1', 'Value 1', 'Other 1'),
  (101, 'Name 2', 'Value 2', 'Other 2'),
  (102, 'Name 3', 'Value 3', 'Other 3'),
  (103, 'Name 4', 'Value 4', 'Other 4');

But how it works in Update, or if I have to loop the query and update one by one?

At the moment I have to use foreach to loop and update each SQL statement.

foreach() {
    // update statement....
    // and execute the query
}

Updating multiple rows can be way shorter than insert but at the same time be very dangerous if proper conditions are not supplied. What is it that you are trying to solve, can you add that to the question, hopefully some sample data and some conditions? — vee, Jul 27 '13 at 03:51
Do you wanna use different value in each one or update wuth the same value ? — Alpha Root Mks, Jul 27 '13 at 03:54
@vinodadhikary Thanks yes there is condition. i have associated id attach with it — Bill, Jul 27 '13 at 03:55
You can use `LOAD SQL LOCAL INFILE` type of update but it gets complicated especially when dealing with updating data and not simply appending rows to a table. We'd have to see your table design for any further information. — Ryan, Jul 27 '13 at 04:02

score 1 · Answer 1 · answered Jul 27 '13 at 03:56

1

  $data = array(
    array(
        100, 
        'Name 1',
        'Value 1', 
        'Other 1'
    ),
    array(
        101, 
        'Name 2', 
        'Value 2', 
        'Other 2',
    )
    array(
        102, 
        'Name 3', 
        'Value 3',
        'Other 3'
    )

)

for($i = 0; $i < count($data); $i++) {

for($j= 0; $j< count($data[$i]); $j++) {
    $field1 = $data[$i][$j];
    $field2 = $data[$i][$j];
    $filed3 = $data[$i][$j];
    $field4 = $data[$i][$j];
        query("UPDATE TABLE SET.... WHERE id = $field1");

}
}

answered Jul 27 '13 at 03:56

Ryan

14,392
8
62
102

That's insert RPM I want update. Thanks any how and this not efficiently, look at my insert above – Bill Jul 27 '13 at 03:57
@bluebill1049 see my update -- i've wrote a Pseudo-query showing you how you would do it; – Ryan Jul 27 '13 at 03:59
Thank RPM give u a "UP" anyhow @Pra answer is what i am after. – Bill Jul 27 '13 at 04:04

score 1 · Accepted Answer · edited May 23 '17 at 10:32

1

You can use case when indise update..

An eg:

UPDATE users
    SET value = CASE 
        WHEN id in (1,4) THEN 53
        WHEN id = 2 THEN 65
        WHEN id in (3,5) THEN 47
    END
WHERE id IN (1,2,3,4,5)

Refer this so and this for more.

edited May 23 '17 at 10:32

Community

1
1

answered Jul 27 '13 at 03:59

Praveen Prasannan

7,093
10
50
70

nice this is what i am after. – Bill Jul 27 '13 at 04:03

score 0 · Answer 3 · answered May 01 '18 at 08:04

You can use CASE in MYSQL also:

Ex:

UPDATE example
    SET example_id = CASE 
        WHEN example_id=100 THEN 1000
        WHEN example_id=101 THEN 1001
        WHEN example_id=102 THEN 1002
        ELSE example_id*10
    END

You can see another example, In this, there is a table Person and having wrongly entered Gender. So you need to correct them by updating Male to Female and Female to Male.

So here is your code:

UPDATE Person 
    SET Gender= CASE
        WHEN gender= 'Male' THEN 'Female' 
        WHEN gender= 'Female' THEN 'Male' 
        ELSE gender 
    END

Mysql Update mutiple row of datas

3 Answers3

Linked