apprentice apprentice - 7 months ago 25
SQL Question

Error in SQL query (SQL update)

The query is :

Update t1
set t1.paper_attempt = 1
from table1 as t1
JOIN table2 as t2
ON t2.user_id = t1.user_id
JOIN table3 as t3
ON t3.id = t2.company_id
where t3.candidate_id = 'CAND024';


I am using HeidiSQL, on running the query, it is showing a Syntax error. Please help!

Answer

Your syntax doesn't work in mySQL.

The common multi-table update query is:

UPDATE [LOW_PRIORITY] [IGNORE] table_list
SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ...
[WHERE where_condition]

i.e. your query should be rewritten as:

Update table1 as t1, 
       table2 as t2, 
       table3 as t3
set  t1.paper_attempt = 1
where  t2.user_id = t1.user_id
        and t3.id = t2.company_id
        and t3.candidate_id = 'CAND024';

Also you can use subquery:

Update table1 as t1
set  t1.paper_attempt = 1
where  t1.user_id in (
       select t2.user_id
       from table2 as t2
       join table3 as t3 on (t3.id = t2.company_id)
       where t3.candidate_id = 'CAND024'
);