MySQL new fields and assignment-CodePudding

A SQL statement, situation is as follows:
There is a table:
Id, did, cid, value
1, 1, 1, 232,
2, 1, 2, 323,
3, 1, 3, 434,
4, 2, 1, 333,
5, 2, 2, 434,
6, 2, 3, 321

Then I added a column c1_value:
Id, did, cid, value, c1_value
1, 1, 1, 232, 0
2, 1, 2, 323, 0
3, 1, 3, 434, 0
4, 2, 1, 333, 0
5, 2, 2, 434, 0
6, 2, 3, 321, 0

Now I would like to give c1_value assignment, the value of did the same cid=1 the value of the value, and then I will delete all of the data of cid=1,
The result should be like this:
Id, did, cid, value, c1_value
1, 1, 1, 232 232
2, 1, 2, 323, 232
3, 1, 3, 434, 232
4, 2, 1, 333 333
5, 2, 2, 434, 333
6, 2, 3, 321, 333

CodePudding user response:

 
Select * 
, (the select LTX. Value from ta LTX 
Where LTX. Cid=1 and LTX. Did=t.d id 
Limit 1) c1_value 
The from ta t;