sql - MySQL batch queries with limit -

I have to change the ownership of many properties in my MySQL table. The things that I am going to do is that there are many similar things and I do not want to change them all.

Now, I am doing this with questions:

UPDATE property SET citizen_id = 1 where citizen_id = 2 and good_id = 8 LIMIT 1 update property SET citizen_id = 2 where citizen_id = 4 and good_id = 2 LIMIT 1 update property SET citizen_id = 4 where citizen_id = 3 and good_id = 5 LIMIT 2

There are some time, many of them and they All are so similar that I think there should be a way to submit them to the batch. Illustrations I can see that show batch updates do not allow individual updates to be set as individual updates like each update.

Do you know in any way that I can do this process faster?

The following method depends on the fact that the primary key in the property table And citizen_id is not part of it. This idea is:

1. Keep all the parameters of the update (to filter on citizen_id and good_id , the new value < Code> citizen_id and the number of rows to update) in some storage, a dedicated table, perhaps, or in a temporary table.

2. The row number is to divide the property rows to (citizen_id, good_id) , then on citizen_id To filter the original full set, include the row ranked on the parameter table, and the number of rows along with good_id .

3. Join property and update citizen_id with the primary key values ​​and new values.

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

  include the updated asset as P (SELECT @r: = @r * (@c = p.citizen_id and @g = p.good_id) + 1 as R, p.possession_id, @c: = p.citizen_id as citizen_id, @g: = P.GO D_id AS good_id properties Add as a P (SELECT @r: = 0, @ c: = 0, @g: = 0) x as the order p.citizen_id, p.good_id) as F. p.possession_id = f Possession_id INNER Obtain rights in u_youtube_id = f.citizen_id and u.good_id = f.good_id SET p.citizen_id = u.new_citizen_id WHERE fr & lt; = U.row_count; The  

property_updated parameter is the table of values.

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

I do not have MySQL, so I can not test it properly from the display point, but at least you can see that the method works (the UPDATE statement is in the schema script, because the SQL Fleet MySQL The right side just gives the post-up content of property .)