Move Cell Values from a Table Column to another
---Take a look of the join tables before the move
SELECT *
FROM vtigercrm600.vtiger_accountbillads
LEFT JOIN vtigercrm600.vtiger_accountshipads
ON vtiger_accountbillads.accountaddressid = vtiger_accountshipads.accountaddressid
WHERE vtiger_accountshipads.ship_street !="" AND vtiger_accountbillads.bill_street=""
---Copy the Values
UPDATE vtigercrm600.vtiger_accountbillads
LEFT JOIN vtigercrm600.vtiger_accountshipads
ON vtiger_accountbillads.accountaddressid = vtiger_accountshipads.accountaddressid
SET vtiger_accountbillads.bill_street=vtiger_accountshipads.ship_street
WHERE vtiger_accountbillads.bill_street="" AND vtiger_accountshipads.ship_street != "";
---Erase the copied values (or the values are the same in both cells)
UPDATE vtigercrm600.vtiger_accountbillads
LEFT JOIN vtigercrm600.vtiger_accountshipads
ON vtiger_accountbillads.accountaddressid = vtiger_accountshipads.accountaddressid
SET vtiger_accountshipads.ship_street=""
WHERE vtiger_accountbillads.bill_street=vtiger_accountshipads.ship_street;
---Validation of all transfers
SELECT * #vtiger_accountbillads.accountaddressid,vtiger_accountbillads.bill_code,vtiger_accountshipads.ship_code
FROM vtigercrm600.vtiger_accountbillads
LEFT JOIN vtigercrm600.vtiger_accountshipads
ON vtiger_accountbillads.accountaddressid = vtiger_accountshipads.accountaddressid
WHERE (vtiger_accountshipads.ship_city != "" AND vtiger_accountbillads.bill_city="")
OR (vtiger_accountshipads.ship_code != "" AND vtiger_accountbillads.bill_code="")
OR (vtiger_accountshipads.ship_country != "" AND vtiger_accountbillads.bill_country="")
OR (vtiger_accountshipads.ship_state != "" AND vtiger_accountbillads.bill_state="")
OR (vtiger_accountshipads.ship_pobox != "" AND vtiger_accountbillads.bill_pobox="")
OR (vtiger_accountshipads.ship_street != "" AND vtiger_accountbillads.bill_street="");
---Take a look of the join tables before the move
SELECT *
FROM vtigercrm600.vtiger_accountbillads
LEFT JOIN vtigercrm600.vtiger_accountshipads
ON vtiger_accountbillads.accountaddressid = vtiger_accountshipads.accountaddressid
WHERE vtiger_accountshipads.ship_street !="" AND vtiger_accountbillads.bill_street=""
---Copy the Values
UPDATE vtigercrm600.vtiger_accountbillads
LEFT JOIN vtigercrm600.vtiger_accountshipads
ON vtiger_accountbillads.accountaddressid = vtiger_accountshipads.accountaddressid
SET vtiger_accountbillads.bill_street=vtiger_accountshipads.ship_street
WHERE vtiger_accountbillads.bill_street="" AND vtiger_accountshipads.ship_street != "";
---Erase the copied values (or the values are the same in both cells)
UPDATE vtigercrm600.vtiger_accountbillads
LEFT JOIN vtigercrm600.vtiger_accountshipads
ON vtiger_accountbillads.accountaddressid = vtiger_accountshipads.accountaddressid
SET vtiger_accountshipads.ship_street=""
WHERE vtiger_accountbillads.bill_street=vtiger_accountshipads.ship_street;
---Validation of all transfers
SELECT * #vtiger_accountbillads.accountaddressid,vtiger_accountbillads.bill_code,vtiger_accountshipads.ship_code
FROM vtigercrm600.vtiger_accountbillads
LEFT JOIN vtigercrm600.vtiger_accountshipads
ON vtiger_accountbillads.accountaddressid = vtiger_accountshipads.accountaddressid
WHERE (vtiger_accountshipads.ship_city != "" AND vtiger_accountbillads.bill_city="")
OR (vtiger_accountshipads.ship_code != "" AND vtiger_accountbillads.bill_code="")
OR (vtiger_accountshipads.ship_country != "" AND vtiger_accountbillads.bill_country="")
OR (vtiger_accountshipads.ship_state != "" AND vtiger_accountbillads.bill_state="")
OR (vtiger_accountshipads.ship_pobox != "" AND vtiger_accountbillads.bill_pobox="")
OR (vtiger_accountshipads.ship_street != "" AND vtiger_accountbillads.bill_street="");
If you want to be updated about similar snippets,
Sign in
and follow our
Channels