CODEWITHSUNDEEP
sqlsql-server
Furkan Gözükara
Furkan Gözükara

Reputation: 23870

How to select a variable and update another variable with that selected variable in single query?

I want to select top 1 row from a table and update a column of that selected row at the same query. So this is the select query I am using

SELECT TOP 1 PageId,
             PageUrl,
             CrawlDepth
FROM   tblPages
WHERE  MainSiteId = 1
       AND UnExpectedCrawlError = 0
       AND CrawlStatus = 0
       AND CrawlingStarted = 0

Now this query returns 1 row. What I want is update this to update the CrawlingStarted column of this row and set it to 1. So I will select a row and update its 1 column at the same time.

How can I do that?

Upvotes: 0

Views: 71

Answers (1)

usr
usr

Reputation: 171246

You can use an update statement and use an OUTPUT clause.

UPDATE TOP (1) t
SET CrawlingStarted = 1
OUTPUT inserted.PageId, inserted.PageUrl, inserted.CrawlDepth
FROM   tblPages t
WHERE  MainSiteId = 1
       AND UnExpectedCrawlError = 0
       AND CrawlStatus = 0
       AND CrawlingStarted = 0

Upvotes: 2

Related Questions