question

brendagrossnickle-6913 avatar image
0 Votes"
brendagrossnickle-6913 asked TomCooper-6989 answered

Updating a table by joining on ROW_NUMBER using modulo function

I need to generate some demo data. In this example I have a Customer table that needs to get updated from a table with random values. Trying to create some tsql that can be used if the Customer table has more rows, less rows, or more rows than the random table. I am assuming that each table has a ROW_NUMBER (RECID). The problem is that the way I have it written i have to add a +1 to the WHERE statement depending on which file has more rows. Want to make the tsql flexible enough that one WHERE clause will work for all three examples. Any help is appreciated.

  • when random_names has more rows than customer
    where customer.recid%(@random_names_rowcnt) = random_names.recid%(@customer_rowcnt) + 1;

  • when customer has more rows than random_names
    where customer.recid%(@random_names_rowcnt) + 1 = random_names.recid%(@customer_rowcnt) ;

  • when customer and random_names have the same number of rows
    where customer.recid%(@random_names_rowcnt) = random_names.recid%(@customer_rowcnt) ;

    / setup example /

    IF OBJECT_ID('customer', 'U') IS NOT NULL
    drop table customer
    create table customer (recid int, cust_name text)
    insert into customer (recid) values (1), (2), (3), (4), (5);

    IF OBJECT_ID('random_names', 'U') IS NOT NULL
    drop table random_names
    create table random_names (recid int, name text);
    insert into random_names (recid, name) values (1,'beth'), (2,'bob'), (3,'gary'), (4,'linda'), (5,'olivia'), (6,'francis'), (7,'simone'), (8,'westmonte'), (9,'gail');

    / example #1 - more rows in random table than customer table /

    declare @customer_rowcnt int = (select count() from customer);
    declare @random_names_rowcnt int = (select count(
    ) from random_names);

    update customer set cust_name = random_names.name
    from random_names
    where customer.recid%(@random_names_rowcnt) = random_names.recid%(@customer_rowcnt) + 1;

    select * from customer;

    / example #2 - equal number of rows in each table /

    delete from random_names where recid > 5;
    update customer set cust_name = '';

    declare @customer_rowcnt int = (select count() from customer);
    declare @random_names_rowcnt int = (select count(
    ) from random_names);

    update customer set cust_name = random_names.name
    from random_names
    where customer.recid%(@random_names_rowcnt) = random_names.recid%(@customer_rowcnt);

    select * from customer;

    / example #3 - more rows in customer table than random table /

    delete from random_names where recid > 3;
    update customer set cust_name = '';

    declare @customer_rowcnt int = (select count() from customer);
    declare @random_names_rowcnt int = (select count(
    ) from random_names);

    update customer set cust_name = random_names.name
    from random_names
    where customer.recid%(@random_names_rowcnt) + 1 = random_names.recid%(@customer_rowcnt);

    select * from customer;


sql-server-transact-sql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Viorel-1 avatar image
0 Votes"
Viorel-1 answered Viorel-1 commented

Check if the next script works for all of the cases:

 declare @random_names_rowcnt int = (select count(*) from random_names)
    
 update customer 
 set cust_name = random_names.name
 from customer, random_names
 where (customer.recid - 1) % @random_names_rowcnt = random_names.recid - 1

It assumes that RECID was obtained by ROW_NUMBER.

By the way, the operation does not look random.

· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Works like a charm. thanks.

0 Votes 0 ·

i realize that it is not random. any ideas on how to make it random?

0 Votes 0 ·
Viorel-1 avatar image Viorel-1 brendagrossnickle-6913 ·

I think that this requires a different approach, and it is probably better to start a new question.

0 Votes 0 ·
TomCooper-6989 avatar image
0 Votes"
TomCooper-6989 answered brendagrossnickle-6913 commented

One way to use @Viorel-1 technique to get a random output is

 ;With cteCustomer As
 (Select recid, cust_name, NEWID() As custRndID
 From customer),
    
 cteCustSorted As
 (Select recid, cust_name, Row_Number() Over(Order By custRndID) As Newrecid
 From cteCustomer),
    
 cteNames As
 (Select recid, name, NEWID() As nameRndID
 From random_names),
    
 cteNamesSorted As
 (Select recid, name, Row_Number() Over(Order By nameRndID) As Newrecid
 From cteNames)
    
 update cteCustSorted
 set cust_name = cteNamesSorted.name
 from cteCustSorted, cteNamesSorted
 where (cteCustSorted.Newrecid - 1) % @random_names_rowcnt = cteNamesSorted.Newrecid - 1;

Tom

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

while it is more random, in my testing, it still results in the same results. meaning if there are 15 customers and 5 random_names that each customer will get 3 of the random_names with and without the NEWID(). without the NEWID() it will be the same predictable repeated pattern. WIth the NEWID() it will not be a predictable pattern, but it seems the number of each name used will be the same. Predictable pattern is ok for me. i created the recid (identity column) specifically just for use in creating random data. it will be dropped.

0 Votes 0 ·
TomCooper-6989 avatar image
0 Votes"
TomCooper-6989 answered

If you want to make each name assigned completely random, you can do

 declare @random_names_rowcnt int = (select count(*) from random_names)
    
 Declare @RandomNumbers Table(recid int, Random_Number int);
    
 Insert @RandomNumbers(recid, Random_Number)
 Select c.recid, Abs(Cast(Cast(NewID() AS VARBINARY) AS INT)) 
         % (@random_names_rowcnt) + 1
 From customer c;
    
 Update c
 Set cust_name = r.name
 From customer c
 Inner Join @RandomNumbers rn On c.recid = rn.recid
 Inner Join random_names r On r.recid = rn.Random_Number;

Tom

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.