CODEWITHSUNDEEP
phpmysql
Omari Victor Omosa
Omari Victor Omosa

Reputation: 2879

how to update database sequentially using mysql

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)");

Upvotes: 1

Views: 127

Answers (2)

Solarflare
Solarflare

Reputation: 11106

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.

Upvotes: 2

e4c5
e4c5

Reputation: 53774

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.

Upvotes: 3

Related Questions