Using Update whilst using Select in SQL

my SQL programming is limited which is why im having problem with this probably basic code. I am trying to update a record as im retrieving it, I know I can update it if I split it into two procedures but I would like to update the records that it is retreiving for consistancy sake. Anyway below is the code and hopefully it will show what I am trying to accomplish:

Update dbo.ASNs
    Set Sent = 'Yes'
    Where ASNNumber In
(
    Select * From dbo.ASNs
    Where Sent = 'No'
    开发者_如何学Cfor xml auto, ELEMENTS, ROOT('ASNs')
)

I think you mean this:

UPDATE dbo.ASNs
SET Sent = 'Yes'
OUTPUT inserted.*
WHERE Sent = 'No'
FOR XML auto, ELEMENTS, ROOT('ASNs')

See here

the IN clause is wrong, you can´t compare a column with a *.

...Where ASNNumber In (Select * From dbo.ASNs...

this should be something like:

...Where ASNNumber In (Select ASNNumber  From dbo.ASNs ...

Try looking at using UPDATE with an OUTPUT INTO.

Without any other optimizations:

UPDATE  dbo.ASNs
SET     Sent = 'Yes'
FROM    dbo.ASNs
WHERE   ASNNumber IN ( SELECT   ASNNumber
                       FROM     dbo.ASNs
                       WHERE    Sent = 'No'
        FOR     XML AUTO ,
                    ELEMENTS ,
                    ROOT('ASNs') )