How to update a table from another one depending on certain conditions and getting data from a third one if the conditions are not met?

Question

I need to update a table with values coming from another table while statisfying a few conditions in the meantime.

To be more specific, the 'source' field of table1 needs to get updated by the field 'value' from table2. These 2 tables share a common 'id' field that can be used for a join. If the 'id' of table1 doesn't have any correspondence in table2, then it should take the value of the 'source2' field from table3. If the 'source2' value is 'NULL', then it should be given a default value that we will name 'Default' in this example.

As an example, we have the table1 below, which is the one we want to update:

Then we have the table2 below, which is the source table that will be used to update table1:

Finally we have table3, which will be used if the information in table2 is unavailable:

Based on this example, I would like to write a query that would update table1 with the values below:

I have written the following query using MariaDB but obviously it is not correct:

UPDATE table1 T1
LEFT JOIN table2 T2 ON T1.id = T2.id
LEFT JOIN table3 a ON T1.id = T3.id
SET T1.source = if(T2.value is NULL, if(T3.source is NULL, 'Default', T3.source), T2.value) 
WHERE T1.id = T2.id

Which parts should be amended to make it work?

Answer 1

You seem quite close. The left join logic is fine, we just need to adjust the conditional set and the where clause:

update table1 t1
left join table2 t2 on t2.id = t1.id
left join table3 t3 on t3.id = t1.id
set t1.source = coalesce(t2.value, t3.source, 'Default')
where t2.id is not null or t3.id is not null

coalesce() returns its first non-null argument.

The where clause ensures that we don't update rows that match in neither tables. You can remove it if you want to update all rows (those that do not match will get 'Default' assigned).