How to update certain column in sql using select and where clause on the same table?-CodePudding

I have a table called employeepostinghistory with the following columns: employee_posting_id, employee_posting_to, employee_posting_from, emp_emp_cnic

employee_posting_id is primary key and emp_emp_cnic is foreign key

Basically this table is responsible to hold employee history with from and to dates i.e. employee_posting_from and employee_posting_to.

I want to update all records setting employee_posting_to=NULL Where employee history is latest so, I have used DESC on employee_posting_from. But, UPDATE query says subquery return more than 1 record, What can be the possible solution for this problem.

UPDATE employeepostinghistory 
SET employeepostinghistory.posting_to=NULL 
WHERE employeepostinghistory.emp_emp_cnic=(
    SELECT DISTINCT employeepostinghistory.emp_emp_cnic 
    from employeepostinghistory  
    GROUP BY employeepostinghistory.emp_emp_cnic 
    ORDER BY employeepostinghistory.posting_from DESC
    )
;

CodePudding user response：

UPDATE employeepostinghistory 
NATURAL JOIN ( 
    SELECT emp_emp_cnic, MAX(datetime_column) datetime_column
    FROM employeepostinghistory 
    GROUP BY 1 
    ) last_row_per_employee
SET employeepostinghistory.posting_to=NULL 
;

emp_emp_cnic.datetime_column must be unique.