Perform UPDATE on two relational tables

Question

I have an address table with auto generated ID column, and this id is referenced in customer table, I cannot perform update operation using the code below

 UPDATE mydb.adrs
            SET adrs_col1 = _adrs_col1, 
                adrs_col2 = _adrs_col2
            FROM mydb.adrs AS ad
            JOIN mydb.customer AS cu  
            ON ad.adrs_id = cu.adrs_adrs_id
            WHERE customer_id = _customer_id;

_customer_id is the passed in ID for a customer _adrs_col1 is the passed in Address _adrs_col2 is also the passed in Address (this is second address)

For this kind of operation you want to use a transaction and two different update statements. Watch out for deadlocks! — Hogan, Feb 12 '21 at 17:53

score 1 · Accepted Answer · edited Feb 12 '21 at 20:33

1

In MySQL you should use this syntax for update with join for a passed in customer ID, as the ID already exists and therefor justifies update operation

UPDATE mydb.adrs ad 
JOIN mydb.customer AS cu  
ON ad.adrs_id = cu.adrs_adrs_id
SET adrs_col1 = _adrs_col1, 
    adrs_col2 = _adrs_col2
WHERE customer_id = _customer_id;

edited Feb 12 '21 at 20:33

answered Feb 12 '21 at 17:54

ScaisEdge

131,976
10
91
107

This will update the customer table? – Hogan Feb 12 '21 at 17:55
add a clear data sample and the expcted result .. i-m not in your db and i can't know which table you want update .. antway the query updated adrs but in mysql you can update also two table – ScaisEdge Feb 12 '21 at 17:56
ummm... hello I'm not the original poster :) – Hogan Feb 12 '21 at 17:57
@Hogan . i'm sorry .. .anyway .. this query just update the table mention in update mydb.adrs so if you need another table you can change the reference in update clause .. if you need update several table you can add the tables name comma separated ..hope my comment is clear and useful . and sorry again – ScaisEdge Feb 12 '21 at 18:03
@scaisEdge I have tried that, but i want to execute that with the where clause, because i want to perform the update on a passed in customer id – Feb 12 '21 at 20:29
the where clause is in the ON clause . is the same – ScaisEdge Feb 12 '21 at 20:31

Perform UPDATE on two relational tables

1 Answers1