Aren’t correlation IDs ok in MSSQL for an UPDATE statement?
I'm getting the following error with the attached SQL statment on MSSQL:
UPDATE IMMUNE I
Software/Hardware used:
MSSQL
UPDATE IMMUNE I
Software/Hardware used:
MSSQL
ASKED:
September 2, 2009 4:35 PM
UPDATED:
September 2, 2009 10:37 PM
Answer Wiki:
Why do you need the alias when you are referencing only one tabe ?
You can use aliases in updates, but I think it should be something like ths:
<pre>UPDATE IMMUNE
SET FLDENCOUNTR =
(SELECT MIN(E.FLDREC_NUM)
FROM ELOG E INNER JOIN EMPLOYEE M
ON E.FLDEMPLOYEE = M.FLDREC_NUM
WHERE E.FLDDATE = I.FLDDATE
AND M.FLDREC_NUM = I.FLDEMPLOYEE)
<b>FROM INMUNE I</b>
WHERE I.FLDENCOUNTR IS NULL
AND EXISTS
(SELECT *
FROM ELOG E INNER JOIN EMPLOYEE M
ON E.FLDEMPLOYEE = M.FLDREC_NUM
WHERE E.FLDDATE = I.FLDDATE
AND M.FLDREC_NUM = I.FLDEMPLOYEE)</pre>
Also, I'd change this:
<pre>...AND EXISTS
(SELECT * ...</pre>
for this:
<pre>...AND EXISTS
(SELECT <b>1...</b> </pre>
-----------------------------
To see all answers submitted to the Answer Wiki: View Answer History.
I’ll try that – thanks! Just FYI, our site uses aliases as a standard. Even though there’s one table, the data for the update comes from another table that has the same field names (“Date”). Thanks!
It seems that the original question was truncated.
DeloeranGuy, can you post your original question again (here in the discussion section) for future reference, when you have time ?
Thanks.