mysql-update from another table recordno 20141103

SELECT DISTINCT ROW_NO,SALES_ORDER_INV_DESC.PARENT_RECORDID
FROM SALES_ORDER_INV_DESC
LEFT JOIN SALES_ORDER_HEAD ON SALES_ORDER_HEAD.RECORDID=SALES_ORDER_INV_DESC.PARENT_RECORDID
WHERE SALES_ORDER_HEAD.SUBJECT LIKE '%Search String%'AND SALES_ORDER_INV_DESC.ROW_NO=0;

+--------+-----------------+
| ROW_NO | PARENT_RECORDID |
+--------+-----------------+
|      0 |   1422930063870 |
+--------+-----------------+

SET @MTARGET:=   1422930063870;
SELECT ROW_NO,DESCRIPTION FROM SALES_ORDER_INV_DESC WHERE PARENT_RECORDID= @MTARGET ;
SET @rank:=0;
UPDATE SALES_ORDER_INV_DESC SET ROW_NO=@rank:=@rank+1 WHERE PARENT_RECORDID= @MTARGET  AND ROW_NO=0;
SELECT ROW_NO,DESCRIPTION FROM SALES_ORDER_INV_DESC WHERE PARENT_RECORDID= @MTARGET;

DROP TABLE IF EXISTS DUMMYX;
CREATE TABLE DUMMYX SELECT RECORDID,CUS_SHORT_FORM FROM GRNBODY;
UPDATE SUPPLIER_INVOICE_LINE
INNER JOIN DUMMYX
ON DUMMYX.RECORDID=SUPPLIER_INVOICE_LINE.GRN_LINE_RECORDID
SET SUPPLIER_INVOICE_LINE.CUS_SHORT_FORM=DUMMYX.CUS_SHORT_FORM;
DROP TABLE DUMMYX;






 select RECORDID FROM DNOTE_HEADER WHERE DNOTE_NO LIKE '%3278%';
 SELECT RECORDID FROM DNOTE_LINE WHERE PARENT_RECORDID=1433748884403;
 create table DNOTE_LINE_BAK_2 SELECT * FROM DNOTE_LINE;



update RECEIPT_LINE
INNER JOIN SALES_HEADER
ON SALES_HEADER.RECORDID=RECEIPT_LINE.INV_RECORDID
SET RECEIPT_LINE.SIGNED_INV=SALES_HEADER.SIGNED_INV;



UPDATE VENDOR_PRODUCT
INNER JOIN PRODUCT
ON PRODUCT.RECORDID=VENDOR_PRODUCT.PARENT_RECORDID
SET VENDOR_PRODUCT.VENDOR_PARTNO=PRODUCT.MODEL
WHERE VENDOR_PRODUCT.VENDOR_PARTNO='';


update GRN_LINE
INNER JOIN GRN_HEADER
ON GRN_HEADER.RECORDID=GRN_LINE.PARENT_RECORDID
SET GRN_LINE.CERT='N/A'
WHERE  GRN_HEADER.STATUS='P';




UPDATE SALES_ORDER_HEAD
INNER JOIN TEMP
ON TEMP.RECORDID=SALES_ORDER_HEAD.RECORDID
SET SALES_ORDER_HEAD.TIMESTAMP=TEMP.TIMESTAMP;



UPDATE RECEIPT_HEADER
INNER JOIN RECEIPT_LINE
ON RECEIPT_LINE.PARENT_RECORDID=RECEIPT_HEADER.RECORDID
SET RECEIPT_HEADER.DESCRIPTION=RECEIPT_LINE.DESCRIPTION;

UPDATE SALES_ORDER_PO_DESC_BAK
SET TIMESTAMP=TIMESTAMP, PARENT_RECORDID=
(SELECT PARENT_RECORDID FROM SALES_ORDER_PO_DESC WHERE RECORDID=SALES_ORDER_PO_DESC_BAK.RECORDID;

UPDATE SALES_ORDER_INV_DESC_BAK
SET TIMESTAMP=TIMESTAMP, PARENT_RECORDID=
(SELECT PARENT_RECORDID FROM SALES_ORDER_PO_DESC WHERE RECORDID=SALES_ORDER_INV_DESC_BAK.RECORDID;

Updating entry WITHOUT updating timestamp

DROP TABLE DUMMY0;
CREATE TABLE DUMMY0 SELECT SOH_RECORDID, RECORDID  FROM GRNLIST;
UPDATE GRN_HEADER SET TIMESTAMP=TIMESTAMP, SOH_RECORDID=(SELECT DISTINCT SOH_RECORDID FROM DUMMY0 WHERE DUMMY0.RECORDID=GRN_HEADER.RECORDID);
DROP TABLE DUMMY0;




 SET @rank:=0;
 UPDATE PRODUCT SET NO=@rank:=@rank+1;

update table set name='test-name' order by id desc limit 1;
update table set name='text name' where id = (select max(id) from table);
update table set field=(select max field)+1 from table) where id in (1,3,5,6,8);

UPDATE RECEIPT_HEADER
SET DESCRIPTION =
(SELECT DESCRIPTION
FROM RECEIPT_LINE
WHERE RECEIPT_LINE.PARENT_RECORDID = RECEIPT_HEADER.RECORDID
);


update from another table with MAX *** 

UPDATE SALES_ORDER_HEAD
SET LAST_UPDATE_DATE = 
(SELECT MAX(timestamp) 
FROM MESSAGE_HEADER
WHERE MESSAGE_HEADER.PARENT_RECORDID = SALES_ORDER_HEAD.RECORDID
);
delimiter $$


update from another table

UPDATE SUPPLIER_INVOICE_LINE
INNER JOIN GRNQTYOS
ON GRNQTYOS.GRN_LINE_RECORDID=SUPPLIER_INVOICE_LINE.GRN_LINE_RECORDID
SET SUPPLIER_INVOICE_LINE.GRN_DDATE=GRNQTYOS.GRN_DDATE;


UPDATE RECEIPT_HEADER
SET DESCRIPTION =
(SELECT DESCRIPTION
FROM RECEIPT_LINE
WHERE RECEIPT_LINE.PARENT_RECORDID = RECEIPT_HEADER.RECORDID
);



UPDATE RECEIPT_LINE
INNER JOIN DUMMY0
ON DUMMY0.RECORDID=RECEIPT_LINE.INV_RECORDID
SET RECEIPT_LINE.DESCRIPTION=DUMMY0.DESCRIPTION;

UPDATE ADDRESS_BOOK
INNER JOIN VENDOR
ON VENDOR.NAME=ADDRESS_BOOK.COMPANY
SET ADDRESS_BOOK.PARENT_RECORDID = VENDOR.RECORDID;

UPDATE ADDRESS_BOOK
INNER JOIN CUSTOMER
ON CUSTOMER.NAME=ADDRESS_BOOK.COMPANY
SET ADDRESS_BOOK.PARENT_RECORDID = CUSTOMER.RECORDID;


UPDATE VENDOR_PRODUCT
INNER JOIN PRODUCT
ON PRODUCT.RECORDID=VENDOR_PRODUCT.PARENT_RECORDID
SET VENDOR_PRODUCT.VENDOR_PARTNO=PRODUCT.MODEL
WHERE VENDOR_PRODUCT.VENDOR_PARTNO='';

UPDATE PRODUCT
INNER JOIN SETTINGS
ON PRODUCT.MANUFACTURER=SETTINGS.DESCRIPTION
SET PRODUCT.MANUFACTURER = SETTINGS.RECORDID

update-recordno




UPDATE DNOTE_HEADER INNER JOIN DNOTE_LINE ON DNOTE_HEADER.RECORDID= DNOTE_LINE.PARENT_RECORDID
SET DNOTE_HEADER.CUSTOMER_NO = DNOTE_LINE.CUSTOMER_NO;



******************************************
* UPDATE PURCHASAE_HEADER.SQD_FILE
******************************************

select SQD_NO, SQD_FILE FROM SALES_ORDER_PO_DESC;

ALTER TABLE PURCHASE_HEADER ADD COLUMN DUMMY NVARCHAR(200) DEFAULT '';



UPDATE PURCHASE_HEADER
INNER JOIN SALES_ORDER_HEAD
ON SALES_ORDER_HEAD.SALES_ORDER=PURCHASE_HEADER.SALES_ORDER
SET PURCHASE_HEADER.DUMMY = SALES_ORDER_HEAD.RECORDID;


UPDATE PURCHASE_HEADER
INNER JOIN SALES_ORDER_PO_DESC
ON SALES_ORDER_PO_DESC.PARENT_RECORDID=PURCHASE_HEADER.DUMMY
SET PURCHASE_HEADER.SQD_FILE = SALES_ORDER_PO_DESC.SQD_FILE;

UPDATE PURCHASE_HEADER SET DUMMY='';