0

I am facing this scenario: Table Employee has joining_date column. Table Booking has booking_date column and a foreign key (employee_id) to Employee. Employee has some NULL values in its joining_date column. I want to fill them with the FIRST booking_date value of those employees. How can I do?

FYI: I can query with complex join statements to extract the first booking_date of employees whose joining_date is NULL as below:

SELECT emp.employee_id, emp.joining_date, temp2.booking_date FROM employee emp
  LEFT JOIN (SELECT bo.employee_id, bo.booking_date FROM booking bo
    INNER JOIN (SELECT employee_id, MIN(booking_date) mindate FROM booking GROUP BY employee_id) temp1
    ON bo.employee_id = temp1.employee_id AND bo.booking_date = temp1.mindate) temp2
  ON emp.employee_id = temp2.employee_id
WHERE emp.joining_date IS NULL;

But I'm struggling with putting this complex select into the update statement:

UPDATE employee emp
SET emp.joining_date = (SELECT ...)
WHERE emp.joining_date IS NULL;
Truc Pham
  • 3
  • 3

1 Answers1

0

Your select statement is more complex than it needs to be, you will get the same set this way:

SELECT emp.employee_id,min(bo.booking_date) booking_date
FROM employee emp
  LEFT JOIN booking bo 
  ON bo.employee_id = emp.employee_id
WHERE emp.joining_date is NULL
GROUP BY emp.employee_id;

Your update can be done like this, note that the "and exists" section is optional but I tend to include it to make the intent of the query more clear.

UPDATE employee emp
  SET emp.joining_date = 
    (SELECT min(booking_date) from booking bo where bo.employee_id = emp.employee_id)
WHERE emp.joining_date IS NULL
  and exists(select * from booking bo where bo.employee_id = emp.employee_id);
GTG
  • 4,914
  • 2
  • 23
  • 27