CODEWITHSUNDEEP
sqlsql-serversql-update
Paul Gibson
Paul Gibson

Reputation: 634

Update with a complex Select Query

Need help with a SQL query that returns the lasts records matching a certain criteria

I have a query similar to that in the referenced post. I am trying to use it to set another field to 1, just for the cases that are returned with the group and MAX results. So I have tried this:

Update FilesTransmitted
set Accepted = 1
select MAX(f.UID) as UID, f.DocumentNumber, f.REV
from FilesTransmitted f
INNER JOIN FilesReturned r
on f.DocumentNumber = r.[Document #]
and f.REV = r.REV
where NOT(r.CODE IS NULL)
group by f.DocumentNumber, f.REV
order by f.DocumentNumber;

which results in all of the records having the Accepted field set, not just the MAX UID records. I know I am using the update wrong, but the query is complex enough for me that I'm just not sure how to structure it for the update portion.

Upvotes: 1

Views: 82

Answers (2)

Brian DeMilia
Brian DeMilia

Reputation: 13248

Try:

Update FilesTransmitted t
   set Accepted = 1
 where t.uid = (select max(f.UID)
                  from FilesTransmitted f
                  join FilesReturned r
                    on f.DocumentNumber = r.[Document #]
                   and f.REV = r.REV
                 where r.CODE is null
                   and f.DocumentNumber = t.DocumentNumber
                   and f.rev = t.rev
                 group by f.DocumentNumber, f.REV);

Upvotes: 2

DavidG
DavidG

Reputation: 118957

What you have written is essentially 2 queries, add a blank line and you will see what I mean:

Update FilesTransmitted
set Accepted = 1

select MAX(f.UID) as UID, f.DocumentNumber, f.REV
from FilesTransmitted f
INNER JOIN FilesReturned r
on f.DocumentNumber = r.[Document #]
and f.REV = r.REV
where NOT(r.CODE IS NULL)
group by f.DocumentNumber, f.REV
order by f.DocumentNumber;

What you need is a WHERE clause in your UPDATE:

Update FilesTransmitted
set Accepted = 1
WHERE f.UID = ...

Upvotes: 0

Related Questions