How to update row base on column result?

Question

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.

2000+ sir. is there anyway to UPDATE it base on the id like (1,4,5,6,9,10) — kim de castro, Mar 21 '16 at 06:29
You have to somehow provide update values for all those. :( You can use one `update statement` but there in the condition part you have separate the update values for all those entries. How many of these 2000+ entries share same values of `assign_num`? — 1000111, Mar 21 '16 at 06:31
no sir. assign_num (ex. 0000-1112, 0000-1501, 0000-1120) planning to update it manually if there's no way to do it easily. — kim de castro, Mar 21 '16 at 06:32
One by one, with power of a spreadsheet, two columns id, num, and concatenate in third cell ="UPDATE table SET num = '"&B1&"' WHERE id = "&A1&";" — Mauricio Florez, Mar 21 '16 at 06:35
Currently where's the mapping stored? I mean which `assign_num` is mapped for which row? Where it's stored? Could you please share that? — 1000111, Mar 21 '16 at 06:39
to update the assign_num base on the "assign_num given by the customer" like (ex. id 1,4,5 assign_num 0000-1112, 0000-1501, 0000-1120) — kim de castro, Mar 21 '16 at 06:40
It's not clear what the relationship is between assign_num, id and company. Your example has id 2 and 3 using the same assign_num and same company while 4 and 5 have the same company but different assign_num. — christutty, Mar 21 '16 at 06:41
simple way is just keep all assign_number against company in excel and prepare sql statement by excel formula and execute on server.... — Zafar Malik, Mar 21 '16 at 06:47

score 1 · Accepted Answer · answered Mar 21 '16 at 06:48

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.

how can I ORDER BY FULLNAME? – kim de castro Mar 31 '16 at 06:19 — kim de castro, Mar 31 '16 at 06:19

score 0 · Answer 2 · edited May 23 '17 at 11:59

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.

How to update row base on column result?

2 Answers2