mysql - Update table if certain fields are null with related values from a different column -

i writing etl in kettle pentaho create table various sources including google analytics.

so table 1 = data website joined google analytics information table 2 = duplicate data table 1 joined google analytics information

my problem info on table 1 has google analytics information missing table 2 shows data google analytics on same reference_number

so want lookup [reference_number] table 1 table 2 , populate table 1 columns null info on table 2

quick example edit*

table 1 (main table) * *this table has index built in on website_reference number (unique)*   website_reference_number   ga_info_1   ga_info_2    a1              null       null   a2               x           y  table 2 (duplicates table 1)              eventlabel   ga_info_1   ga_info_2   a1               z            z   a2               x            y 

my output should following

table 1 (main table) ref_number   ga_info_1   ga_info_2  a1               z            z a2               x            y 

i using my_sql database

update mytable left join table2 on mytable.ref_number = table2.ref_number set mytable.ga_info_1 = coalesce (     mytable.ga_info_1,     table2.ga_info_1 ),  mytable.ga_info_2 = coalesce (     mytable.ga_info_2,     table2.ga_info_2 )     mytable.ga_info_1 null or mytable.ga_info_2 null 

put fields might null clause.

if field not null not updated because first argument in coalesce function, if null updated field of other table.

edit: can try this:

update mytable inner join table2 on mytable.ref_number = table2.ref_number set mytable.ga_info_1 = coalesce (     mytable.ga_info_1,     table2.ga_info_1 ),  mytable.ga_info_2 = coalesce (     mytable.ga_info_2,     table2.ga_info_2 )     concat(mytable.ga_info_1, mytable.ga_info_2) null 

for performance issue: (as mentioned in comments)

since not using primary or foreign keys join tables, have set index on ref_number columns speed join.