Converting Select query to Update

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,
JerryYou can try the following:
UPDATE B
SET B.DateSigned = C.dateEntered
FROM B
inner join ( 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%'
) C
on C.ItemID = B.RecordNumber
You may want to check the join clause to make sure you match the records
exactly. In any case, the idea is to use derived tables, which is the one
that is created on-the-fly using the SELECT statement, and referenced just
like a regular table or view
Let me know if it helps
"JerryK" wrote:

> 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
>
>|||something like this (completely untested):
UPDATE B
SET DateSigned=(
SELECT C.dateEntered
FROM A INNER JOIN
C ON A.itemid = C.recordid LEFT OUTER JOIN
D ON A.itemid = D.DemoRecordID
WHERE D.itemid IS NULL
and C.action like '%signed%'
and A.itemid = B.RecordNumber )
WHERE status = 'app'
dean
"JerryK" <jerryk@.nospam.com> wrote in message
news:%23S1clJfIGHA.2668@.tk2msftngp13.phx.gbl...
> 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
>|||On Wed, 25 Jan 2006 13:00:37 -0800, JerryK wrote:
(snip)
Hi Jerry,
I just answered this question in microsoft.public.sqlserver.newusers.
In the future, please post your questions to one group only. And if you
really feel that a question should be in two groups, crosspost it (i.e.
send one copy to both groups at the same time) instead of sending
independent copies to the groups. With crossposting, all replies will
(normally) show up in both groups as well. That saves others the time
and energy to find an answer if the question already was answered
elsewhere!
Hugo Kornelis, SQL Server MVP