apprentice apprentice - 2 years ago 99
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 = 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 Source

Your syntax doesn't work in mySQL.

The common multi-table update query is:

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 = 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 ( = t2.company_id)
       where t3.candidate_id = 'CAND024'
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download