generate dynamic update statement

  • 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

  • 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.

  • 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.

    Executive Junior Cowboy Developer, Esq.[/url]

  • This got me where I wanted to go.

    http://www.mssqltips.com/sqlservertip/2914/rolling-up-multiple-rows-into-a-single-row-and-column-for-sql-server-data/

    Reason behind all of this is separation of duties.

  • Erin. (8/14/2014)


    This got me where I wanted to go.

    http://www.mssqltips.com/sqlservertip/2914/rolling-up-multiple-rows-into-a-single-row-and-column-for-sql-server-data/

    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?

  • oops



    Alvin Ramard
    Memphis PASS Chapter[/url]

    All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

    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]

  • 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 = 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

    Doesn't this do the same as:

    UPDATE DListing SET Status = 1 WHERE Status = 2



    Alvin Ramard
    Memphis PASS Chapter[/url]

    All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

    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 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply