mysql-update (another table, sequence number, update control by select table

Update Row No

delimiter $$
drop procedure updatePAYMENT_LINERowNo $$
CREATE PROCEDURE updatePAYMENT_LINERowNo ()
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE mrow INT ;
declare mRECORDID varchar(20);
declare mPARENT_RECORDID varchar(20) DEFAULT '';
declare mLAST_PARENT_RECORDID varchar(20) DEFAULT '';
DEClARE PAYMENT_LINE_cursor CURSOR FOR SELECT PARENT_RECORDID,RECORDID FROM PAYMENT_LINE ORDER BY PARENT_RECORDID;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
OPEN PAYMENT_LINE_cursor;
get_recordid: LOOP
FETCH PAYMENT_LINE_cursor INTO mPARENT_RECORDID, mRECORDID;
IF done THEN
LEAVE get_recordid;
END IF;
/**********/
IF mPARENT_RECORDID<>mLAST_PARENT_RECORDID THEN
SET mLAST_PARENT_RECORDID=mPARENT_RECORDID;
SET MROW=1;
END IF;
UPDATE PAYMENT_LINE SET ROW_NO=MROW WHERE RECORDID=MRECORDID;
SET MROW=MROW+1;
/**********/
END LOOP get_recordid;
END;
$$
DELIMITER ;
call updatePAYMENT_LINERowNo ();
temp2

drop procedure updateGRN_LINERowNo


Update the latest timestamp

DROP TABLE DUMMY0;
CREATE TABLE DUMMY0 AS
SELECT * FROM (SELECT TIMESTAMP, PARENT_RECORDID FROM MESSAGE_HEADER WHERE 1
ORDER BY TIMESTAMP DESC
) AS T1 GROUP BY PARENT_RECORDID;

UPDATE PURCHASE_HEADER SET TIMESTAMP=
(SELECT TIMESTAMP FROM DUMMY0 WHERE DUMMY0.PARENT_RECORDID=PURCHASE_HEADER.RECORDID);


SELECT TIMESTAMP FROM PURCHASE_HEADER;

DROP TABLE DUMMY0;



Update the latest user name

DROP TABLE DUMMY0;
CREATE TABLE DUMMY0 AS
SELECT * FROM (SELECT USER, PARENT_RECORDID FROM MESSAGE_HEADER WHERE 1
ORDER BY TIMESTAMP DESC
) AS T1 GROUP BY PARENT_RECORDID;
SELECT USER,PARENT_RECORDID FROM DUMMY0 WHERE PARENT_RECORDID=1439363483474 GROUP BY PARENT_RECORDID DESC;
UPDATE SALES_ORDER_INV_DESC SET BOM_SUBMITTED_BY =
(SELECT USER FROM DUMMY0 WHERE DUMMY0.PARENT_RECORDID=SALES_ORDER_INV_DESC.RECORDID);
DROP TABLE DUMMY0;

update PRODUCT, (SELECT PARENT_RECORDID FROM VENDPROD WHERE VENDOR_NO=1413967052033) SRC
SET  STATUS='r' WHERE PRODUCT.RECORDID=SRC.PARENT_RECORDID;


SET @rank:=201503131400000;
INSERT INTO MESSAGE_HEADER (RECORDID,PARENT_RECORDID, ACTION,CONTENT )
SELECT @rank:=@rank+1, PARENT_RECORDID PARENT_RECORDID , 'REVOKE' ACTION,'Change Supplier to ????'  FROM VENDPROD WHERE VENDOR_NO=1413967052033;S





set @i=0;
 UPDATE DUMMY0 SET RECORDID=round(unix_timestamp(now())*10+(@i:=@i+1),0);
my-sql update

update USER set module='' where isnull(module);

 update CHANGE_LOG SET DATE=CURDATE() ,TIME=CURTIME(),USER='';

create trigger ai_eav
after insert on eav
for each row
begin
set @id=new.entity;
set @attribute=new.attribute;
set @value=new.value;
update pivot
set 
Author=(select if(@attribute='Author',@value,Author)),
Title=(select if(@attribute='Title',@value,Title)),
Publisher=(select if(@attribute='Publisher',@value,Publisher))
where
id=@id;
end



my-sql update from another table




update ir56b,taxDoc set ir56b.employeerecordid=taxDoc.employeerecordid  where ir56b.recordid=taxDoc.recordid;

my-sql update sequence number

set @i=0;

insert into PRODUCT (DESCRIPTION
,RECORDID,COMMENT
,CATEGORY, MAINTAIN_TYPE,DUMMY1, NO_2,manufacturer )
value (
'Product Name'
,round(unix_timestamp(now())*10+(@i:=@i+1),0),''
,'Software','N/A',' ','item number', 'Company Name ');