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 IIF
:
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
results
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