Reputation: 609
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
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
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
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