Conditional update retrieved from another table - Access syntax error
I am starting with MS Access.
I have two tables - workrelationship gives dates for every time the person has worked in the organization (we seem to stop and retype a lot) and workterms gives start and end dates for every time something has changed in the system for an employee - a new address, change of name, etc. Thus, there can be several work periods during a work period.
There's a POS (service period id) column in workrelationship that I need to copy to workterms for each wt date range that falls in the wr date range. Then I need to delete every row in jobs where dates do not fall in the wr range.
There are ~ 50k rows in workterms and I need to get it right, so I set up example tables in Access to reproduce. Here's a link to two tables and an error message (see below).
My plan is to insert the POS code first and mark anything that is not limited by the "delete" working relationship dates. Then I can do a delete on every line that says "delete" in the POS column. (
DELETE FROM workterms WHERE pos = 'delete';
I am getting a syntax error in Access when I do the first part:
UPDATE workterms SET wt.pos = CASE WHEN (wt.termstart >= wr.originalstart and wt.termend <= wr.finalend) THEN wr.pos ELSE 'delete' END FROM workterms wt INNER JOIN workrelationship wr ON wr.personid = wt.personid
Any ideas on a syntax error? It highlights the word "WHEN" on line 3. I'm not familiar with Access, but I've searched and it looks correct compared to what I found.
EDIT: I've tried it too
UPDATE workterms SET wt.pos = IIF ((wt.termstart >= wr.originalstart and wt.termend <= wr.finalend), wr.pos, 'delete') FROM workterms wt INNER JOIN workrelationship wr ON wr.personid = wt.personid;
EDIT 2: The HansUp request below went great (thanks!) Which takes care of the syntax issue.
UPDATE workterms wt INNER JOIN workrelationship wr ON wr.personid = wt.personid SET wt.pos = IIF( wt.termstart >= wr.originalstart AND wt.termend <= wr.finalend, wr.pos, 'delete' );
EDIT 3: But - it only gave 3 values in where I expect 8 as you can see by the selection below and the "SELECT from EDIT 3" tab in the linked spreadsheet:
SELECT wr.*, wt.* FROM workrelationship wr, workterms wt WHERE wr.personid = wt.personid and wt.termstart >= wr.originalstart and wt.termend <= wr.finalend
source to share
Your current UPDATE query is not giving you the results you want because the INNER JOIN is only executed on [personid]. So, for any [personid] that appears more than once in [workrelationship], your UPDATE query produces several conflicting results.
Consider the following SELECT query that mimics the behavior of your UPDATE query for [personid] = 2:
SELECT IIF( wt.termstart >= wr.originalstart AND wt.termend <= wr.finalend, wr.pos, 'delete' ) AS result, wt.personid, wt.termstart, wt.termend, wt.pos AS wt_pos, wr.originalstart, wr.finalend, wr.pos AS wr_pos FROM workterms wt INNER JOIN workrelationship wr ON wr.personid = wt.personid WHERE wt.personid = 2 ORDER BY wt.termstart, wr.originalstart
result personid termstart termend wt_pos originalstart finalend wr_pos ------ -------- ---------- ---------- ------ ------------- ---------- ------ 200-1 2 2010-02-01 2010-03-01 blah 2010-02-01 2010-05-01 200-1 delete 2 2010-02-01 2010-03-01 blah 2010-07-01 2010-10-01 200-2 200-1 2 2010-03-01 2010-05-01 blah 2010-02-01 2010-05-01 200-1 delete 2 2010-03-01 2010-05-01 blah 2010-07-01 2010-10-01 200-2 delete 2 2010-07-01 2010-10-01 blah 2010-02-01 2010-05-01 200-1 200-2 2 2010-07-01 2010-10-01 blah 2010-07-01 2010-10-01 200-2
Note that each [workterms] line appears twice, once for each corresponding line in [workrelationship], and the [result] differs depending on which [workrelationship] line is connected. Thus, your UPDATE query will actually update each line [workterms] twice. The final value of wt.pos will be whichever value is applied last, and the order in which updates are applied is entirely up to the query optimizer.
So what you need to do is apply the updates in two passes. Start by setting all wt.pos values to 'delete'
UPDATE workterms SET pos = 'delete'
and then update the wt.pos values for the eight (8) rows you want to store using a query that joins both [personid] and date values:
UPDATE workterms wt INNER JOIN workrelationship wr ON wr.personid = wt.personid AND wt.termstart >= wr.originalstart AND wt.termend <= wr.finalend SET wt.pos = wr.pos
Now the [workterms] table will look like this:
personid termstart termend pos -------- ---------- ---------- ------ 1 2010-08-01 2010-09-01 100-1 1 2010-03-01 2010-05-01 100-1 2 2010-07-01 2010-10-01 200-2 2 2010-03-01 2010-05-01 200-1 2 2010-02-01 2010-03-01 200-1 3 2010-11-01 2015-12-01 delete 3 2010-09-01 2010-10-01 300-2 3 2010-08-01 2010-09-01 300-2 3 2010-07-05 2010-07-15 delete 3 2010-05-01 2010-07-01 300-1
source to share