locked
Updating multiple rows with random values from another table RRS feed

  • Question

  • I'm trying to update multiple rows in one column and one table with a random values from another table.  Every where I searched suggested something like this:

    UPDATE table1   SET column1 =
    (SELECT TOP 1 columnA FROM table2
    ORDER BY NEWID())

    However all this does is select a random value from table2 and sets every row in column1/table1 to that single random value... where what I want is every row to have a randomly selected value.

    (SQL 2005)

    Thursday, October 15, 2009 2:23 AM

Answers

  • Like this:

     
    ;
    WITH cteTable1 AS (
        SELECT
            ROW_NUMBER() OVER (ORDER BY NEWID()) AS n,
            colum1
        FROM table1
        ),
    cteTable2 AS (
        SELECT
            ROW_NUMBER() OVER (ORDER BY NEWID()) AS n,
            columnA
        FROM table2
        )
    UPDATE cteTable1
       SET column1 = (
           SELECT columnA 
           FROM cteTable2
           WHERE cteTable1.n = cteTable2.n)
    

    This works for SQL Server 2005.  If you need it for SQL Server 2000, let me know and I can convert it.


    -- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
    Proactive Performance Solutions, Inc.
    "Performance is our middle name."

    Please! Remember to Vote all helpful replies as Helpful
    Thursday, October 15, 2009 12:57 PM
  • RBarryYoung,

    I have voted your post as helpful. Good thinking.


    Sakendrick,

    CTE is a new feature in SQL Server 2005 and it helps to hold the results in a virtual table to avoid temp tables and derived tables in the past.


    RBarryYoung solution will work but it may not be working as one expected because of the data distribution in your tables. I am afraid that it will update only 7 records in the big table as the small table has only 7 records.

    Here is another version to solve this and it will work in SQL Server 2000 also.

    create table dbo.bigtable (c1 datetime, c2 int identity(1,1))
    go
    insert dbo.bigtable
    	select distinct create_date
    	from sys.objects cross join sys.columns
    go 
    
    create table dbo.smalltable (c1 datetime, c2 int identity(1,1))
    go
    insert dbo.smalltable
    	select distinct top 7 create_date
    	from sys.objects cross join sys.columns
    go
    
    select * from dbo.bigtable
    select * from dbo.smalltable
    
    
    DECLARE @maxRandomValue TINYINT 
    		, @minRandomValue TINYINT 
    
    SELECT @maxRandomValue = 7, @minRandomValue = 0;
    
    select CAST(((@maxRandomValue + 1) - @minRandomValue) * RAND(CHECKSUM(NEWID())) + @minRandomValue AS TINYINT) as cID, c1 
    into #smalltable 
    from dbo.smalltable order by CAST(((@maxRandomValue + 1) - @minRandomValue) * RAND(CHECKSUM(NEWID())) + @minRandomValue AS TINYINT)
    
    select CAST(((@maxRandomValue + 1) - @minRandomValue) * RAND(CHECKSUM(NEWID())) + @minRandomValue AS TINYINT) as cID, c1 
    into #bigtable 
    from dbo.bigtable order by CAST(((@maxRandomValue + 1) - @minRandomValue) * RAND(CHECKSUM(NEWID())) + @minRandomValue AS TINYINT)
    
    select * from #bigtable
    select * from #smalltable
    
    update big
    set big.c1 = small.c1
    from #bigtable big join #smalltable small
    on big.cID = small.cID
    
    go
    --cleanup now
    drop table dbo.bigtable, dbo.smalltable
    drop table #bigtable, #smalltable
    



    | Sankar Reddy | http://sankarreddy.spaces.live.com/ |
    Thursday, October 15, 2009 5:03 PM
  • That's a good point, Sankar, I had missed that there where only 7 records in table2.  Here is a correction to my original query that should fix that:

    ;
    WITH cteTable1 AS (
        SELECT
            ROW_NUMBER() OVER (ORDER BY NEWID()) AS n,
            colum1
        FROM table1
        ),
    cteTable2 AS (
        SELECT TOP (3000)    -- set this to whatever you need
            ROW_NUMBER() OVER (ORDER BY NEWID()) AS n,
            t21.columnA
        FROM table2 t21, table2 t22, table2 t23, table2 t24, table2, t25
        )
    UPDATE cteTable1
       SET column1 = (
           SELECT columnA 
           FROM cteTable2
           WHERE cteTable1.n = cteTable2.n
           )
    



    -- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
    Proactive Performance Solutions, Inc.
    "Performance is our middle name."

    Please! Remember to Vote all helpful replies as Helpful
    Thursday, October 15, 2009 9:33 PM

All replies

  • It would help if you mention the data types of the columns and the # of rows in the tables and how frequently this is run.
    | Sankar Reddy | http://sankarreddy.spaces.live.com/ |
    Thursday, October 15, 2009 4:13 AM
  • You have to use trick programmming, it is called multiple-value assignment.  Sample follows, see the SET clause:

    -- Create table with select into
    USE tempdb;
    SELECT * 
    INTO   Prod 
    FROM   AdventureWorks2008.Production.Product 
    
    GO 
    
    -- Multiple-value assignment update
    DECLARE  @u UNIQUEIDENTIFIER 
    
    UPDATE Prod 
    SET    @u = rowguid = newid() 

    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012


    • Edited by Kalman Toth Wednesday, October 3, 2012 6:18 PM
    Thursday, October 15, 2009 6:08 AM
  • Like this:

     
    ;
    WITH cteTable1 AS (
        SELECT
            ROW_NUMBER() OVER (ORDER BY NEWID()) AS n,
            colum1
        FROM table1
        ),
    cteTable2 AS (
        SELECT
            ROW_NUMBER() OVER (ORDER BY NEWID()) AS n,
            columnA
        FROM table2
        )
    UPDATE cteTable1
       SET column1 = (
           SELECT columnA 
           FROM cteTable2
           WHERE cteTable1.n = cteTable2.n)
    

    This works for SQL Server 2005.  If you need it for SQL Server 2000, let me know and I can convert it.


    -- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
    Proactive Performance Solutions, Inc.
    "Performance is our middle name."

    Please! Remember to Vote all helpful replies as Helpful
    Thursday, October 15, 2009 12:57 PM
  • data type in column1 and column A are datetime (recall I'm just trying to randomly populate table1.column1 with values from table2.columnA)
    There are about 3000 rows in table1 and 7 rows in table2

    Thursday, October 15, 2009 1:08 PM

  • This works for SQL Server 2005.  If you need it for SQL Server 2000, let me know and I can convert it.


    Pls. do so.
    Kalman Toth, SQL Server & BI Training, SSAS, SSIS, SSRS; http://www.SQLUSA.com
    Thursday, October 15, 2009 1:09 PM
  • RBarryYoung... a few questions for clarification... looks like you reference 4 tables in the above.  cteTable1, cteTable2, table1, and table2.  Can you explain what these other tables are?
    Thursday, October 15, 2009 1:21 PM
  • RBarryYoung... a few questions for clarification... looks like you reference 4 tables in the above.  cteTable1, cteTable2, table1, and table2.  Can you explain what these other tables are?
    table1 and table2 are your tables, as you named them in your example.

    cteTable1 and cteTable2 are the aliases for the CTE's (Common Table Expressions) defined in the WITH clause that are adding the random row-numbering to table1 and table2 respectively.


    -- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
    Proactive Performance Solutions, Inc.
    "Performance is our middle name."

    Please! Remember to Vote all helpful replies as Helpful
    Thursday, October 15, 2009 1:35 PM
  • Note: I have edited the code in my previous post to correct a possible code problem (ie., it needs that leading semicolon, if the previous statement was not terminated with a semicolon).

    -- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
    Proactive Performance Solutions, Inc.
    "Performance is our middle name."

    Please! Remember to Vote all helpful replies as Helpful
    Thursday, October 15, 2009 1:44 PM
  • This works for SQL Server 2005.  If you need it for SQL Server 2000, let me know and I can convert it.
    Pls. do so.
    Kalman Toth, SQL Server & BI Training, SSAS, SSIS, SSRS; http://www.SQLUSA.com
    Hmm, my bad.  I was thinking only of the CTE's which can be downgraded to SQL 2000 as subqueries.  The ROW_NUMBERS() however are a bit more difficult and not directly translatable here, so it would probably be a complete rewrite, at least...

    -- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
    Proactive Performance Solutions, Inc.
    "Performance is our middle name."

    Please! Remember to Vote all helpful replies as Helpful
    Thursday, October 15, 2009 1:53 PM
  • RBarryYoung,

    I have voted your post as helpful. Good thinking.


    Sakendrick,

    CTE is a new feature in SQL Server 2005 and it helps to hold the results in a virtual table to avoid temp tables and derived tables in the past.


    RBarryYoung solution will work but it may not be working as one expected because of the data distribution in your tables. I am afraid that it will update only 7 records in the big table as the small table has only 7 records.

    Here is another version to solve this and it will work in SQL Server 2000 also.

    create table dbo.bigtable (c1 datetime, c2 int identity(1,1))
    go
    insert dbo.bigtable
    	select distinct create_date
    	from sys.objects cross join sys.columns
    go 
    
    create table dbo.smalltable (c1 datetime, c2 int identity(1,1))
    go
    insert dbo.smalltable
    	select distinct top 7 create_date
    	from sys.objects cross join sys.columns
    go
    
    select * from dbo.bigtable
    select * from dbo.smalltable
    
    
    DECLARE @maxRandomValue TINYINT 
    		, @minRandomValue TINYINT 
    
    SELECT @maxRandomValue = 7, @minRandomValue = 0;
    
    select CAST(((@maxRandomValue + 1) - @minRandomValue) * RAND(CHECKSUM(NEWID())) + @minRandomValue AS TINYINT) as cID, c1 
    into #smalltable 
    from dbo.smalltable order by CAST(((@maxRandomValue + 1) - @minRandomValue) * RAND(CHECKSUM(NEWID())) + @minRandomValue AS TINYINT)
    
    select CAST(((@maxRandomValue + 1) - @minRandomValue) * RAND(CHECKSUM(NEWID())) + @minRandomValue AS TINYINT) as cID, c1 
    into #bigtable 
    from dbo.bigtable order by CAST(((@maxRandomValue + 1) - @minRandomValue) * RAND(CHECKSUM(NEWID())) + @minRandomValue AS TINYINT)
    
    select * from #bigtable
    select * from #smalltable
    
    update big
    set big.c1 = small.c1
    from #bigtable big join #smalltable small
    on big.cID = small.cID
    
    go
    --cleanup now
    drop table dbo.bigtable, dbo.smalltable
    drop table #bigtable, #smalltable
    



    | Sankar Reddy | http://sankarreddy.spaces.live.com/ |
    Thursday, October 15, 2009 5:03 PM
  • That's a good point, Sankar, I had missed that there where only 7 records in table2.  Here is a correction to my original query that should fix that:

    ;
    WITH cteTable1 AS (
        SELECT
            ROW_NUMBER() OVER (ORDER BY NEWID()) AS n,
            colum1
        FROM table1
        ),
    cteTable2 AS (
        SELECT TOP (3000)    -- set this to whatever you need
            ROW_NUMBER() OVER (ORDER BY NEWID()) AS n,
            t21.columnA
        FROM table2 t21, table2 t22, table2 t23, table2 t24, table2, t25
        )
    UPDATE cteTable1
       SET column1 = (
           SELECT columnA 
           FROM cteTable2
           WHERE cteTable1.n = cteTable2.n
           )
    



    -- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
    Proactive Performance Solutions, Inc.
    "Performance is our middle name."

    Please! Remember to Vote all helpful replies as Helpful
    Thursday, October 15, 2009 9:33 PM
  • How about my solution? It appears to be the simplest?
    Kalman Toth, SQL Server & BI Training, SSAS, SSIS, SSRS; http://www.SQLUSA.com
    Thursday, October 15, 2009 9:46 PM
  • How about my solution? It appears to be the simplest?
    Kalman Toth, SQL Server & BI Training, SSAS, SSIS, SSRS; http://www.SQLUSA.com
    But it does not pull it's value from table2, does it?  That's what was specifically requested, a randomly selected value.  I agree, if you just want a random guid, then your solution is the way to go, but AFAIK, that's not what the OP requested.

    -- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
    Proactive Performance Solutions, Inc.
    "Performance is our middle name."

    Please! Remember to Vote all helpful replies as Helpful
    Thursday, October 15, 2009 10:06 PM