Mysql batch update data-CodePudding

According to query the data packet according to the time descending update data

As shown in figure:
Now want to xiao Ming sort=1, the small red sort=2, jack Bauer sort=1
Could you tell me how to write SQL to achieve to

CodePudding user response:

Update into 1 2 what is the logic relationship, corresponding to the name of the fixed?

CodePudding user response:

Mysql> Select * from a;
+ -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - +
| | id name | d | sort |
+ -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - +
Xiao Ming | 1 | | 2017-12-20 07:33:02 | NULL |
| 3 | jack Bauer | 2017-12-20 07:33:19 | NULL |
| 2 | | small red 2017-12-20 07:33:30 | NULL |
+ -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - +
Update a a1 set sort=(select count (*) from (select * from a) a2 where a2. DSelect * from a;
+ -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - +
| | id name | d | sort |
+ -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - +
Xiao Ming | 1 | | 2017-12-20 07:33:02 | 1 |
| 3 | jack Bauer | 2017-12-20 07:33:19 | 2 |
| 2 | | small red 2017-12-20 07:33:30 | 3 |
+ -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - +

Attention to mysql subquery is a bit weird, directly use
Update a a1 set sort=(select count (*) from a a2 where a2 dERROR: ERROR 1093 (HY000) : You can 't specify target table' a1 'for update in the FROM clause
So be more than a layer of
Also do field name is have a problem with the date

CodePudding user response:

The order by the date, the same date related to the use of the sort?

CodePudding user response:

 CREATE TABLE table1 (int id, name varchar (10), the date a datetime, sort int) 
AS 
SELECT 1 as id, as the name 'aa', 'the 2017-12-1 17:00 as date UNION ALL 
2 as id, SELECT 'BB' as the name, 'the 2017-12-2 18:00 as date UNION ALL 
SELECT 3 as id, as the name 'CC', 'the 2017-12-1 13:00 as date 
; 
The UPDATE table1. A, (
SELECT id, sort the FROM (
SELECT 
@ id:=CASE WHEN TIMESTAMPDIFF (day, @ dt, date)=0 THEN @ id ELSE @ id + 1 END as sort, 
@ dt:=date, 
Id 
The FROM table1, (SELECT @ dt:=NULL, @ id:=0) x 
The ORDER BY the date 
B) 
B) 
The SET A.s ort=B.s ort 
WHERE Anderson, d=B.i d 
; 
SELECT * FROM table1;

CodePudding user response:

reference 1st floor wandier response:

update into 1 2 what is the logic relationship, corresponding to the name of the fixed?

According to group_id grouping in ascending order according to the time after the query result set sort

CodePudding user response:

CKC quote 2 floor response:

mysql> Select * from a;
+ -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - +
| | id name | d | sort |
+ -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - +
Xiao Ming | 1 | | 2017-12-20 07:33:02 | NULL |
| 3 | jack Bauer | 2017-12-20 07:33:19 | NULL |
| 2 | | small red 2017-12-20 07:33:30 | NULL |
+ -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - +
Update a a1 set sort=(select count (*) from (select * from a) a2 where a2. DSelect * from a;
+ -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - +
| | id name | d | sort |
+ -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - +
Xiao Ming | 1 | | 2017-12-20 07:33:02 | 1 |
| 3 | jack Bauer | 2017-12-20 07:33:19 | 2 |
| 2 | | small red 2017-12-20 07:33:30 | 3 |
+ -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - +

Attention to mysql subquery is a bit weird, directly use
Update a a1 set sort=(select count (*) from a a2 where a2 dERROR: ERROR 1093 (HY000) : You can 't specify target table' a1 'for update in the FROM clause
So be more than a layer of
Also do field name is have a problem with the date of

Also according to xiao Ming and small red group_id group_id grouping is the same, according to the set time ascending sort, jack Bauer group_id and others is not the same as the group_id so he sort of=1
Must first grouping based on time