问题描述:

I'm looking to perform an update in SQL that will update a specific number of rows with a date then move on to the next group and date.

For example, let's say I have 4000 rows in a subscriptions table. I need to populate the first 100 rows with today's date in the suspended_on field, then the next 100 rows with tomorrow, the next 100 with the day after, until there are no more rows in the suspended_on field.

not sure where to begin, sorry...

网友答案:

for first 1000 rows

UPDATE subscriptions a 
LEFT JOIN (
  SELECT ID,@row := @row+1 row_num 
  FROM subscriptions, (SELECT @row :=0 ) vars order by ID
) b on a.ID = b.ID set a.suspended_on = '02/22/2014'
WHERE @row <= 1000
ORDER BY ID

next 1000 records

UPDATE subscriptions a 
LEFT JOIN (
  SELECT ID,@row := @row+1 row_num 
  FROM subscriptions, (SELECT @row :=0 ) vars order by ID
) b on a.ID = b.ID set a.suspended_on = '02/23/2014'
WHERE @row BETWEEN 1000 AND 2000
ORDER BY ID
相关阅读:
Top