SQL Server Performance Forum – Threads Archive
Using Update and select in same queryI’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…
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. —
Microsoft SQL Server MVP
Thanks for the suggestions – will follow up and post my experiences