How to update colum A2 of table A (colA1, colA2) with data of B2 if table B(colB1, colB2) has matching value of table A.?

Question

I have 2 tables: Table A(ColA1, ColA2), Table B(ColB1. ColB2).

ColA1    ColA2   |     ColB1     ColB2
 1               |      1         abc
 2               |      2         def
 4               |      3         ghi
 7               |      4         jkl
                 |      7         xxx

Now i want to update Col2 with ColB2 where it has matching values of ColA1=ColB1. So, the output should be like:

ColA1    ColA2   |     ColB1     ColB2
 1         abc   |      1         abc
 2         def   |      2         def
 4         jkl   |      3         ghi
 7         xxx   |      4         jkl

How can i do that without procedure?

Answer 1

Yes, you can do this with a JOIN. Here's an example from something I did yesterday...

update compress_test c  
join information_schema.tables t      
on c.table_schema = t.table_schema and c.table_name = t.table_name    
set c.compress_mb = Round(( t.DATA_LENGTH+t.INDEX_LENGTH)/1024/1024)  

where t.table_schema = 'test'
and t.row_format='COMPRESSED';

so you want something like:

update a  
join b on a.cola = b.cola  
set a.colb = b.colb   
where a.colb is null

Though I'm not sure how you are getting these values:

ColA1    ColA2   
 4         jkl   
 7         xxx 

They should still be blank/null based on what you were asking.