CODEWITHSUNDEEP
mysqlsql
Adam Strudwick
Adam Strudwick

Reputation: 13159

MySQL Join clause in Update

When trying to perform this query:

>mysql_query("UPDATE contracts 
              SET x = '1' 
              FROM contracts 
              INNER JOIN employees 
              ON contracts.contract_employeeid=employees.employee_id 
              WHERE experience >= '6'") or die(mysql_error());

I get the following error message:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near FROM contracts INNER JOIN employees ON contracts.contract_employeeid=employees.employee_id WHERE experience >= '6'

In words, I need to set x=1 on the table "contracts" for the employees who have more than 6 years of experience (to do so, I need to join with the table "employees" on employee_id=contract_employeeid since their experience is stored in that table)

Upvotes: 4

Views: 1462

Answers (2)

Salman Arshad
Salman Arshad

Reputation: 272426

You can probably move the JOIN part of your query before the SET statements:

UPDATE contracts
INNER JOIN employees ON contracts.contract_employeeid = employees.employee_id
SET x = '1'
WHERE experience >= '6'

Upvotes: 4

Joe Stefanelli
Joe Stefanelli

Reputation: 135938

UPDATE contracts, employees
    SET contracts.x = '1'
    WHERE contracts.contract_employeeid=employees.employee_id
        AND employees.experience >= '6'

Upvotes: 1

Related Questions