August 14, 2014 at 1:42 pm
I'm trying to generate an update statement based off select statement results. A very basic example,
SELECT ListID FROM DListing WHERE Status = 2
Results return 3 rows.
1234
2345
3456
How can I take those results and turn them into WHERE criteria for UPDATE statement?
Generated UPDATE statement should look like this.
UPDATE DListing SET Status = 1 WHERE ListID IN (1234,2345,3456)
I have started by creating a temp table to hold my SELECT results, but I don't know how to get those results into format for IN condition. Right now I get 3 UPDATE statements with 1 ListID in each IN condition.
CREATE TABLE #TempStatusUpdate(ListID INT)
INSERT INTO #TempStatusUpdate
SELECT ListID FROM DListing WHERE Status = 2
SELECT 'UPDATE DListing SET Status = 1 WHERE ListID IN (' + CONVERT(VARCHAR(30),ListID) + ') AND Status = 2'
DROP TABLE #TempStatusUpdate
August 14, 2014 at 1:49 pm
One way would be to convert your data to a table and use that as your criteria. As you found out, you cannot use a variable as the IN.
Another way would be to make the whole SQL a variable and use sp_executesql.
August 14, 2014 at 3:36 pm
You shouldn't be looking to serialize the results (unless you're doing something like a dynamic pivot, or just formatting things for results. You can do a join in your update clause and avoid having to dynamically create anything.
If the only thing your query depends on is StatusID, you dont even need a subquery or dynamic sql, you can just do this:
update DListing
set [Status] = 1
where [Status] = 2
If it's more complicated than that, you can do a join in your update something like this:
update dl
set [Status] = 1
from DListing dl
inner join (select ListID
from DListing
where [Status] = 2) x
on dl.ListID = x.ListID
If neither of these gets you what you're looking for, please let me know and I'll see if I can address your specifics.
August 14, 2014 at 4:05 pm
This got me where I wanted to go.
Reason behind all of this is separation of duties.
August 15, 2014 at 1:46 pm
Erin. (8/14/2014)
This got me where I wanted to go.Reason behind all of this is separation of duties.
I'm not sure I understand how "separation of duties" has any bearing on the solution. Whether you create a dynamic query based on the result of another query, or just use the query that provides the list values as part of a JOIN isn't going to provide anything remotely resembling separation. Can you please explain further?
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
August 15, 2014 at 1:58 pm
oops
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
August 15, 2014 at 2:01 pm
Erin. (8/14/2014)
I'm trying to generate an update statement based off select statement results. A very basic example,
SELECT ListID FROM DListing WHERE Status = 2Results return 3 rows.
1234
2345
3456
How can I take those results and turn them into WHERE criteria for UPDATE statement?
Generated UPDATE statement should look like this.
UPDATE DListing SET Status = 1 WHERE ListID IN (1234,2345,3456)I have started by creating a temp table to hold my SELECT results, but I don't know how to get those results into format for IN condition. Right now I get 3 UPDATE statements with 1 ListID in each IN condition.
CREATE TABLE #TempStatusUpdate(ListID INT)INSERT INTO #TempStatusUpdate
SELECT ListID FROM DListing WHERE Status = 2
SELECT 'UPDATE DListing SET Status = 1 WHERE ListID IN (' + CONVERT(VARCHAR(30),ListID) + ') AND Status = 2'
DROP TABLE #TempStatusUpdate
Doesn't this do the same as:
UPDATE DListing SET Status = 1 WHERE Status = 2
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply