19

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 ?

Gagantous
  • 432
  • 6
  • 29
  • 69

2 Answers2

31

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'
Paul Maxwell
  • 33,002
  • 3
  • 32
  • 51
  • 1
    why does it use such a dumb syntax? – Joe Phillips Sep 29 '21 at 18:26
  • 1
    There is no answer to that question. Rules of language syntax are just that, rules. They exist so that the instructions may be parsed accurately (and converted into lower level instructions). Subjective assessment on a "dumb" to "clever" scale really won't help adapt to the syntax rules. In the original question the person had included 2 elements between a set of quotations: this simply will not work as you cannot treat a table name (1) and its alias (2) as one string – Paul Maxwell Sep 30 '21 at 08:08
7

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.

Gagantous
  • 432
  • 6
  • 29
  • 69
Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360