none
generate update statement from select results

    Question

  • 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

    Thursday, August 14, 2014 7:46 PM

Answers

All replies

  • So what wrong with

    UPDTATE DListing
    SET     Status = 1
    WHERE   ListID IN (SELECT ListID FROM #TempStatusUpdate)


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Thursday, August 14, 2014 9:50 PM
  • This got me what I wanted.

    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.

    Thursday, August 14, 2014 10:01 PM