MySQL Update Condtional for Three columns Between Two DBs

Question

Having difficulty writing a statement that will allow me to: UPDATE column C in DB1.table1 with data from column B in DB2.table2 IF column B IS NOT null AND column A FROM DB1.table1 = column A FROM DB2.table2 (matching IDs)

Can't tell if I need to use a CASE statement or IF. Appreciate any help, thanks.

You don't need in IF/CASE. You must write according ON clause expression in JOIN/WHERE. — Akina, Oct 19 '21 at 05:41
https://stackoverflow.com/questions/839938/mysql-sql-update-with-correlated-subquery-from-the-updated-table-itself — Ali Fidanli, Oct 19 '21 at 05:57

score 1 · Accepted Answer · answered Oct 19 '21 at 06:28

UPDATE DB1.table1                                 
  JOIN DB2.table2 ON DB2.table2.B IS NOT NULL     -- IF column B IS NOT null
                 AND DB1.table1.A = DB2.table2.A  -- AND column A FROM DB1.table1 
                                                  -- = column A FROM DB2.table2 (matching IDs)
SET DB1.table1.C = DB2.table2.B                   -- UPDATE column C in DB1.table1 
                                                  -- with data from column B in DB2.table2

MySQL Update Condtional for Three columns Between Two DBs

1 Answers1