Mysql Batch Queries With Limit

I need to change the ownership of several possessions in my MySQL table. The thing that's tripping me up is that there are several identical goods and I don't want to change all o

Solution 1:

The following method relies on the fact that the possessions table has a primary key and citizen_id is not part of it. Here's the idea:

1. Put all the parameters of the update (citizen_id and good_id to filter on, the new values of citizen_id and the numbers of rows to update) into some storage, a dedicated table, perhaps, or a temporary table.

2. Assign row numbers to possessions rows partitioning on (citizen_id, good_id), then join the ranked row set to the parameter table to filter the original full set on citizen_id and good_id, as well as the number of rows.

3. Join possessions and the result of the previous join on the primary key values and update citizen_id with the new values.

In MySQL's SQL, the above might look like this:

UPDATE possessions AS p
INNERJOIN
(
  SELECT@r :=@r* (@c= p.citizen_id AND@g= p.good_id) +1AS r,
    p.possession_id,
    @c := p.citizen_id AS citizen_id,
    @g := p.good_id AS good_id
  FROM
    possessions AS p
  CROSSJOIN
    (SELECT@r :=0, @c :=0, @g :=0) AS x
  ORDERBY
    p.citizen_id,
    p.good_id
) AS f ON p.possession_id = f.possession_id
INNERJOIN
  possession_updates AS u ON u.citizen_id = f.citizen_id AND u.good_id = f.good_id
SET
  p.citizen_id = u.new_citizen_id
WHERE
  f.r <= u.row_count
;

The possessions_update is the table containing the parameter values.

The query uses a known method of row numbering that employs variables, which is implemented in the f subquery.

I don't have MySQL so I can't test this properly from the performance point of view, but at least you can see from this SQL Fiddle demo that the method works. (The UPDATE statement is in the schema script, because SQL Fiddle doesn't allow data modification statements in the right-side script for MySQL. The right side just returns the post-UPDATE contents of possessions.)