CODEWITHSUNDEEP
sqlite
Stanford Wong
Stanford Wong

Reputation: 359

SQLite: Alternative for UPDATE JOIN subquery

I have the following SELECT query in SQLite. I'm struggling to figure out how to convert this to a SQLite-friendly UPDATE statement though... I know SQLite UPDATE does not support JOIN. Thank you!

The query is finding the most recent/maximum date for each employee and adding a column with that date to each individual employee

SELECT
  emp.*, maxdate
FROM
  employees emp JOIN (
    SELECT name as newname, MAX(enddate) AS maxdate
    FROM
      employees
    GROUP BY name) m
  ON emp.name = m.newname
  ORDER BY maxdate DESC;

Upvotes: 0

Views: 329

Answers (1)

Dario
Dario

Reputation: 2723

Hmm, if I understand correctly, what you want is

UPDATE employees
        SET enddate = (
             SELECT MAX(enddate)
                  FROM employees e2
                  WHERE e2.name = employees.name
             );

Upvotes: 1

Related Questions