CODEWITHSUNDEEP
mysqlinsert-update
Kelvin Kok
Kelvin Kok

Reputation: 41

Mysql update column based on distinct column

I have a large order table. The order table with a few hundred thousand order records and recently add a new column invoice_no and I need to create the invoice_no value based on distinct (user_id,seller, addtime) for the old order record.

CREATE TABLE `TEST_ORDER`(  
  `ORDER_ID` INT(11),
  `USER_ID` INT(11),
  `SELLER` VARCHAR(50),
  `ADDTIME` INT(11),
  `GOODS_NAME` VARCHAR(50),
  `PRICE` FLOAT(11,2),
  `INVOICE_NO` INT(11)
);

INSERT into `TEST_ORDER` (`ORDER_ID`, `USER_ID`, `SELLER`, `ADDTIME`, 
`GOODS_NAME`, `PRICE`, `INVOICE_NO`)
values('1','1','AMAZON','1399109546','BOOK 1','10',NULL);

INSERT into `TEST_ORDER` (`ORDER_ID`, `USER_ID`, `SELLER`, `ADDTIME`, 
`GOODS_NAME`, `PRICE`, `INVOICE_NO`) 
values('2','1','AMAZON','1399109546','BOOK 2','20','NULL');

INSERT into `TEST_ORDER` (`ORDER_ID`, `USER_ID`, `SELLER`, `ADDTIME`, 
`GOODS_NAME`, `PRICE`, `INVOICE_NO`) 
values('3','2','EBAY','1438582766','BOOK 3','10',NULL);


INSERT into `TEST_ORDER` (`ORDER_ID`, `USER_ID`, `SELLER`, `ADDTIME`, 
`GOODS_NAME`, `PRICE`, `INVOICE_NO`) 
values('4','2','EBAY','1438582766','BOOK 4','20',NULL);


INSERT into `TEST_ORDER` (`ORDER_ID`, `USER_ID`, `SELLER`, `ADDTIME`, 
`GOODS_NAME`, `PRICE`, `INVOICE_NO`) 
values('5','3','AMAZON','1399109546','BOOK 1','10',NULL);

The expected result

enter image description here

Can this be done by normal update sql query or I need to write a store procedure and loop every order record to check the order belong to same invoice_no?

Upvotes: 1

Views: 118

Answers (3)

Abhik Chakraborty
Abhik Chakraborty

Reputation: 44844

Yes this could be done with one update command, however you need to apply some trick to get the running number of invoice using the distinct data and then update with self join. The query would look like

update TEST_ORDER t1
join (
  select 
  ORDER_ID,
  @rn:= if ( (@prev_uid = USER_ID && @prev_seller = SELLER && @prev_addtime = ADDTIME),@rn,@rn+1) as rn,
  @prev_uid:= USER_ID,
  @prev_seller:= SELLER,
  @prev_addtime:= ADDTIME
  from TEST_ORDER , (select @rn:=0,@prev_uid:=0,@prev_seller=null,@prev_addtime:=null)x
  order by USER_ID,SELLER,ADDTIME
)t2
on t1.ORDER_ID = t2.ORDER_ID
set t1.INVOICE_NO = t2.rn

Here is a demo

mysql> select * from TEST_ORDER ;
+----------+---------+--------+------------+------------+-------+------------+
| ORDER_ID | USER_ID | SELLER | ADDTIME    | GOODS_NAME | PRICE | INVOICE_NO |
+----------+---------+--------+------------+------------+-------+------------+
|        1 |       1 | AMAZON | 1399109546 | BOOK 1     | 10.00 |       NULL |
|        2 |       1 | AMAZON | 1399109546 | BOOK 2     | 20.00 |       NULL |
|        3 |       2 | EBAY   | 1438582766 | BOOK 3     | 10.00 |       NULL |
|        4 |       2 | EBAY   | 1438582766 | BOOK 4     | 20.00 |       NULL |
|        5 |       3 | AMAZON | 1399109546 | BOOK 1     | 10.00 |       NULL |
+----------+---------+--------+------------+------------+-------+------------+

mysql> update TEST_ORDER t1
    -> join (
    ->   select 
    ->   ORDER_ID,
    ->   @rn:= if ( (@prev_uid = USER_ID && @prev_seller = SELLER && @prev_addtime = ADDTIME),@rn,@rn+1) as rn,
    ->   @prev_uid:= USER_ID,
    ->   @prev_seller:= SELLER,
    ->   @prev_addtime:= ADDTIME
    ->   from TEST_ORDER , (select @rn:=0,@prev_uid:=0,@prev_seller=null,@prev_addtime:=null)x
    ->   order by USER_ID,SELLER,ADDTIME
    -> )t2
    -> on t1.ORDER_ID = t2.ORDER_ID
    -> set t1.INVOICE_NO = t2.rn;
Query OK, 5 rows affected (0.04 sec)
Rows matched: 5  Changed: 5  Warnings: 0

mysql> select * from TEST_ORDER ;
+----------+---------+--------+------------+------------+-------+------------+
| ORDER_ID | USER_ID | SELLER | ADDTIME    | GOODS_NAME | PRICE | INVOICE_NO |
+----------+---------+--------+------------+------------+-------+------------+
|        1 |       1 | AMAZON | 1399109546 | BOOK 1     | 10.00 |          1 |
|        2 |       1 | AMAZON | 1399109546 | BOOK 2     | 20.00 |          1 |
|        3 |       2 | EBAY   | 1438582766 | BOOK 3     | 10.00 |          2 |
|        4 |       2 | EBAY   | 1438582766 | BOOK 4     | 20.00 |          2 |
|        5 |       3 | AMAZON | 1399109546 | BOOK 1     | 10.00 |          3 |
+----------+---------+--------+------------+------------+-------+------------+
5 rows in set (0.00 sec)

Upvotes: 2

M0rtiis
M0rtiis

Reputation: 3774

I think as variant you can try this

UPDATE `TEST_ORDER` SET `INVOICE_NO` = CRC32(CONCAT(`USER_ID`, `SELLER`, `ADDTIME`))

but.. crc32 will give u alot of dupes.. this will certainly help:

UPDATE `TEST_ORDER` SET `INVOICE_NO` = CONV(SUBSTRING(CAST(SHA(CONCAT(`USER_ID`, `SELLER`, `ADDTIME`)) AS CHAR), 1, 16), 16, 10)

it will have no dupes for millions of records.

also change INVOICE_NO from INT to BIGINT

Upvotes: 1

Amit.S
Amit.S

Reputation: 441

You can set the invoice column value to user id using this statement:

update TEST_ORDER SET INVOICE_NO=USER_ID
where INVOICE_NO IS NULL;

Upvotes: -1

Related Questions