问题描述:

I have 2000 data and all assign number is: 0000-1111 and needs to update by company c.

id | fullname | company | assign_num

1 sample1 company1 0000-1111

2 sample2 company2 0000-1111

3 sample3 company2 0000-1111

4 sample4 company1 0000-1111

5 sample5 company1 0000-1111

the assign_num is given.

how to UPDATE row by COMPANY? (ex. company 1)

ex.

0000-1112, 0000-1501, 0000-1120

result will be like this:

id | fullname | company | assign_num

1 sample1 company1 0000-1112

2 sample2 company2 0000-1111

3 sample3 company2 0000-1111

4 sample4 company1 0000-1501

5 sample5 company1 0000-1120

I can do I manually or find the id if its only 100+ record but its a thousand records to update.

网友答案:

Without windowing functions, MySQL is a bit tricky to get this done with.

This is one version;

UPDATE myTable
JOIN (
  SELECT m1.id, COUNT(m2.id)+1 ord FROM myTable m1
  LEFT JOIN myTable m2 ON m1.id > m2.id AND m1.company = m2.company 
  GROUP BY m1.id
) z1 ON myTable.id = z1.id
JOIN (
  SELECT 1 ord, '0000-1112' assign_num UNION ALL
  SELECT 2 ord, '0000-1501' assign_num UNION ALL
  SELECT 3 ord, '0000-1120' assign_num
) z2
  ON z1.ord = z2.ord
SET myTable.assign_num = z2.assign_num
WHERE myTable.company = 'company1'

Basically, the first subquery assigns a sequence number to each id (lowest id gets 1, next 2 etc.) per company, and the second subquery delivers the data (sequence number 1 should get '0000-1112' and so on). It then just joins the subqueries up and updates myTable with the correct assign_num.

An SQLfiddle to test with.

And, as a reminder, always back up your data before running potentially destructive SQL against your production database.

网友答案:

I don't see any clever way to do this as you basically want to update the assign_num field with different values for each company. But you can also use a script to do so.

Inspired by this answer on another post, you could try the following query :

UPDATE table_name SET assign_num='test'
     WHERE id IN 
         (SELECT id FROM table_name ORDER BY id ASC LIMIT i, 1)      

You will have to set the test and i variable right, but with a little work you should be able to avoid doing it manually. Note that there may be version issues with this query as noted in the comments of the answer I mentioned.

相关阅读:
Top