Search code examples
mysqlsqlsql-updateleft-joincoalesce

Checking several tables when updating table

I am trying to create a script, which will update column lang_id in table static if static.method_id = methods.method_id and if methods.mm_id = languages.mm_id and if languages.is_default = 1. But this one is not working, tell me please, why?

UPDATE s
SET s.lang_id = IFNULL(l.lang_id,1)
FROM static as s
JOIN methods as m
ON m.method_id = s.method_id
JOIN languages as l
ON m.mm_id = l.mm_id AND l.is_default = 1

upd. I am using MySQL Getting error in

'FROM merchants__payment_methods_static as s
JOIN merchants__payment_methods as '

That's what phpMyAdmin tells me

Solution

  • Use LEFT joins:

    UPDATE static s
LEFT JOIN methods m ON m.method_id = s.method_id
LEFT JOIN languages l ON m.mm_id = l.mm_id AND l.is_default
SET s.lang_id = COALESCE(l.lang_id, 1);