CODEWITHSUNDEEP
mysqlsqlsql-update
Sabha
Sabha

Reputation: 609

updating a table field where criteria is from another table

I wish to update all records in a field based on criteria present in another table field. 

Table name: emp_leaves
Field name: leave_limit
common field: emp_id

Table name: emp_summary
Field name: emp_status
common field: emp_id

I wish to run a query in the beginning of every month so that the leave_limit values in the emp_leaves table would increment by 1.5 if the value of emp_status of the emp_summary table is "Permanent"

I tried a few statements from the Internet but it looks like either my logic in incorrect or the syntax I am using is irrelevant. Please help me with how to achieve this. Thanks

Upvotes: 0

Views: 61

Answers (3)

Mansoor
Mansoor

Reputation: 4192

Use Below format :

UPDATE emp_leaves SET emp_leaves.leave_limit = emp_leaves.leave_limit + 1.5
FROM emp_summary 
WHERE emp_leaves.emp_id = emp_summary.emp_id AND emp_summary.emp_status =  
'Permanent'

Upvotes: 1

Akber Ali
Akber Ali

Reputation: 13

You could use this query below to accomplish this:

    update emp_leaves set emp_leaves.leave_limit = emp_leaves.leave_limit + 1.5 from emp_summary where emp_leaves.emp_id = emp_summary.emp_id and emp_summary.emp_status = 'Permanent';

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522817

Use an update join:

UPDATE emp_leaves a 
INNER JOIN emp_summary b
    ON a.emp_id = b.emp_id
SET a.leave_limit = a.leave_limit + 1.5
WHERE b.emp_status = 'Permanent'

Upvotes: 1

Related Questions