how to update database sequentially using mysql

Question

I have a long list from database which i want to update a field by name group. i want to update the first 30 to value '1' next 30 to value '2' and so on until the end

Any idea on how to go about it

$sql = mysqli_query($conn,
   "UPDATE `tablename` SET `group` = '$value' WHERE id IN (SELECT id FROM tablename ORDER BY id desc LIMIT 0, 30)");

@tamil, stored procedures can definitely do this trick but it's probably over kill. — e4c5
– e4c5, Commented Aug 1, 2016 at 8:06
the second answer went well. but i had to repeat the process. i believe there has to be a php code with for i loop. or a do while loop. — Omari Victor Omosa
– Omari Victor Omosa, Commented Aug 8, 2016 at 10:17

e4c5 · Accepted Answer · 2016-08-01 08:06:20Z

3

You have run into this limitation of mysql

You cannot update a table and select from the same table in a subquery.

You could use an update join, but since limits are involved, you would once again run into the same issue. Since only a small number of records are involved, you could do a simple query to get the ids.

SELECT GROUP_CONCAT(id) FROM tablename ORDER BY id desc LIMIT 0, 30

Then use that in the next query

UPDATE `tablename` SET `group` = '$value' WHERE id IN (ids_from_above_query)

If you are worried about concurrent updates, start a transaction.

answered Aug 1, 2016 at 8:06

e4c5

53.9k11 gold badges110 silver badges139 bronze badges

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

Comments

Solarflare · Accepted Answer · 2016-08-01 08:47:09Z

2

If your update is a one-time thing (and don't need to optimize it, e.g. do it all in one query), you can simply repeat the following query until you are finished:

update `tablename` 
SET `group` = '$value' 
where `group` is null
order by id desc
limit 30;

All group-values have to be null (or some other unused value) to begin with, so e.g. use update tablename SET group = null first, if they aren't.

And you probably want to do it all in a transaction.

answered Aug 1, 2016 at 8:47

Solarflare

11.2k2 gold badges20 silver badges37 bronze badges

1 Comment

e4c5 Over a year ago

come to think of it, this is a better answer than mine +1

Collectives™ on Stack Overflow

how to update database sequentially using mysql

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related