How to update a row based a joined table in MariaDB?

Question

I have sql like this:

UPDATE "user_login a" 
    LEFT OUTER JOIN "p_pegawai b" 
    ON a.id_pegawai = b.id  
    SET a.password = 'Keluarga1'  
    WHERE b.NIP = '195812' 

I have tried this : MySql Update A Joined Table

but it always give me error

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '"user_login a" LEFT OUTER JOIN "p_pegawai b" ON a.id_pegawai = b.id SET a.passw' at line 1

I am using MariaDB, not Mysql, what could go wrong with my query ?

Answer 1

Use backticks in MySQL but do not apply these to the combined table and alias they must be treated as separate items

UPDATE `user_login` a 
    LEFT OUTER JOIN `p_pegawai` b
    ON a.id_pegawai = b.id  
    SET a.password = 'Keluarga1'  
    WHERE b.NIP = '195812' 

Answer 2

You are currently placing the entire table names with aliases in double quotes. Remove the double quotes and the update query should work:

UPDATE user_login a 
LEFT JOIN p_pegawai b
    ON a.id_pegawai = b.id
SET a.password = 'Keluarga1'  
WHERE b.NIP = '195812';

While double quotes (along with backticks) are a way to escape a column or table name, you don't need to do this in your case.