SQL Server Performance Forum – Threads Archive

Using Update and select in same query

I’m a SQL novice – please bear with me. I use the ff 2 queries in a home grown application : SQLSTR1 = "SELECT TOP 1 REQTIME, STATUS, DOCID, COPIES,MOBIUS FROM dbo.DOCUMENTS WHERE (STATUS = 3) AND (COPIES=0) ORDER BY REQTIME" and SQLSTR2="UPDATE dbo.Documents SET STATUS="+DocStatus+" WHERE (DOCID = "+"’"+rs1.fields("docid").value+"’"+") AND STATUS=3" (DocStatus has been previously set to a constant) Can this be done with 1 query? i.e. in the form "Update documents set status=Docstatus where (conditions from first query are met) Many thanks in anticipation…

---

Hi,
Consider this query SQLSTR2="UPDATE dbo.Documents SET STATUS="+DocStatus+"
WHERE DOCID = (SELECT TOP 1 DOCID FROM dbo.DOCUMENTS WHERE (STATUS = 3) AND (COPIES=0) ORDER BY REQTIME) AND STATUS=3"

---

You could also use the UPDATE FROM clause. Check out the SQL Server online manual BOL for the complete syntax. —
Frank Kalis
Moderator
Microsoft SQL Server MVP
Webmaster:http://www.insidesql.de

---

Thanks for the suggestions – will follow up and post my experiences

---

]]>