MySQL UPDATE various ways will teach you to use update statement with respect to various problem statement. Some of the problem statement are as follow.
MySQL UPDATE various ways
- Below query will update the first_name to ‘Max’
UPDATE `staff` SET first_name='Max' WHERE staff_id=1
- Instead of updating single record at a time it is good practice to update records in bulk
- For bulk update here we are assigning the policyId to each employee based on their OrgId if OrgId=’1′ then it will assign ‘4’ as policyId.
SET em_employee.policyId = CASE
WHEN TRIM(employee.OrgId) = '1' THEN '4'
WHEN TRIM(employee.OrgId) = '2' THEN '5'
WHEN TRIM(employee.OrgId) = '3' THEN '6'
WHERE em_employee.OrgId IN ('1','2','3');
3.UPDATE with ORDER BY and LIMIT
- If ORDER BY clause is there in UPDATE query in this case rows are updated in the specified ORDER
- LIMIT clause specifies limit on the number of rows that can be updated
- ORDER BY and LIMIT cannot be used for multi table update.
UPDATE employees SET isActive=1 ORDER BY employee_id LIMIT 10
4.Multiple Table UPDATE
- It updates rows in each specified tables that satisfy the conditions
- Each matching row is updated once, even if it matches the conditions multiple times
- The below query will update employee_transaction and attendance_transaction tables respectively with values by setting employee_transaction.status=1 from employee_transaction table and attendance_transaction.process_status from attendance_transaction table.
UPDATE employee_transaction, attendance_transaction
SET employee_transaction.status = '1',
WHERE attendance_transaction.employee_id = employee_transaction.employee_id
AND employee_transaction.joining_date > '2019-04-11'