MySQL database condition batch update?
There are tables:
ID datetime YOULIANG_PROCD
51555 2013-1-18 7:40 173
51556 2013-1-18 7:43 173
51557 2013-1-18 7:50 0
51558 2013-1-18 7:50 0
51572 2013-1-18 8:03 0
51573 2013-1-18 8:04 0
51574 2013-1-18 8:04 0
51575 2013-1-18 8:05 0
51576 2013-1-18 8:11 170
51577 2013-1-18 8:14 170
51578 2013-1-18 8:14 0
51579 2013-1-18 8:15 0
51580 2013-1-18 8:21 167
51581 2013-1-18 8:24 167
51582 2013-1-18 8:30 0
51583 2013-1-18 8:30 0
51584 2013-1-18 8:30 0
Issue: If the field youliang_procd=0, update the value of YOULIANG_PROCD to the value of the previous record, and the result is:
ID datetime YOULIANG_PROCD
51555 2013-1-18 7:40 173
51556 2013-1-18 7:43 173
51557 2013-1-18 7:50 173
51558 2013-1-18 7:50 173
51572 2013-1-18 8:03 173
51573 2013-1-18 8:04 173
51574 2013-1-18 8:04 173
51575 2013-1-18 8:05 173
51576 2013-1-18 8:11 170
51577 2013-1-18 8:14 170
51578 2013-1-18 8:14 170
51579 2013-1-18 8:15 170
51580 2013-1-18 8:21 167
51581 2013-1-18 8:24 167
51582 2013-1-18 8:30 167
51583 2013-1-18 8:30 167
51584 2013-1-18 8:30 167
Can you tell me how to write this SQL statement (in MySQL database), thank you, online and so on!
------Solution--------------------
This must be done with SQL, if you use code, it is more convenient
------Solution--------------------
Can be done directly with SQL
Set @v:=0;
Update Tbl_name, (SELECT ID, @v:=if (youliang_procd,youliang_procd,@v) as u from Tbl_name) t
Set tbl_name.youliang_procd=t.u where tbl_name.id=t.id;
A total of two instructions, in PHP to be executed separately
which
Set @v:=0 is used to declare a user variable
Select ID, @v:=if (youliang_procd,youliang_procd,@v) as u from Tbl_name to bring non-empty YOULIANG_PROCD field values into the next
Tbl_name the name of the table you want to manipulate
------Solution--------------------
SQL completed directly??
------Solution--------------------
Reference:
SQL completed directly??
Yes!
If you do this with PHP, you need to read, judge, modify
This is going to be a very painful process.
