Hi,

I need to update some data based on the results of a select query. The
following select statement returns the values:

SELECT DISTINCT A.itemid, B.DateSigned, B.RefID, C.EnteredBy,
C.Action, B.Status, C.dateEntered
FROM A INNER JOIN
B ON A.itemid = B.RecordNumber INNER JOIN
C ON A.itemid = C.recordid LEFT OUTER JOIN
D ON A.itemid = D.DemoRecordID
WHERE (D.itemid IS NULL)
and B.status = 'app'
and C.action like '%signed%'
ORDER BY A.itemid DESC


What I want to do is update DataSigned as follows.

UPDATE B SET B.DateSigned=C.dateEntered
WHERE ?????

I am not sure how to set up the WHERE clause to update the correct records
with the correct values. Suggestions?

Thanks,

Jerry

[quoted text, click to view]

Hi Jerry,

The easy but dangerous (see below) and non-portable (becuase it uses
proprietary Transact-SQL syntax) way:

UPDATE B
SET DateSigned = C.dateEntered
FROM A INNER JOIN
B ON A.itemid = B.RecordNumber INNER JOIN
C ON A.itemid = C.recordid LEFT OUTER JOIN
D ON A.itemid = D.DemoRecordID
WHERE (D.itemid IS NULL)
and B.status = 'app'
and C.action like '%signed%'

BEWARE: If a row from B is matched to more than one row from C, the end
result will be that B.DateSigned is equal to DateEntered from one of
those rows - but there's no way of knowing in advance which it is, and
it need not be the same on all executions. You won't get an error
message or even a warning about this.

The safe (but harder) way is to use ANSI standard UPDATE syntax:

UPDATE B
SET DateSigned =
(SELECT C.dateEntered
FROM A
INNER JOIN C
ON C.recordid = A.itemid
LEFT JOIN D
ON D.DemoRecordID = A.itemid
WHERE D.itemid IS NULL
AND C.action LIKE '%signed%'
AND A.itemid = B.RecordNumber)
WHERE B.status = 'app'
AND EXISTS
(SELECT *
FROM A
INNER JOIN C
ON C.recordid = A.itemid
LEFT JOIN D
ON D.DemoRecordID = A.itemid
WHERE D.itemid IS NULL
AND C.action LIKE '%signed%'
AND A.itemid = B.RecordNumber)

NOTE: This will result in an error if more than one row in C matches a
row from B. If no row in C matches a row in B, it won't be affected
(like the proprietary version above), but if you remove the AND EXISTS
subquery, each unmatched row in B will have DateSigned set to NULL.

All the queries above are untested. If you prefer a tested reply, see
www.aspfaq.com/5006.

--