RoundRobin update of a table from a second table

Hi team

I have a requirement to create 10 calls per day (Calendar records) each, for a group of Users from a second table in the database CONT1.

I have a table with the user's USERID in table USER.

I  have extracted 10 x the number of current users records from CONT1 as MAKECAL, marking CONT1 as having been used (extracted)

I need to be able to populate the USERID field in the MAKECAL table with USERID from USER table until all the records in MAKECAL have a USERID.  It does not have to be in any order, random order is fine; but a roundrobin update would make more sense.

All assistance is appreciated
Cheers
Bernard
BernardGBaileyAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

RimvisCommented:
Hi BernardGBailey,

Try this code:

SELECT * --Check what will be updated
--UPDATE MAKECAL SET USERID = u.USERID --Uncomment this tu execute actual UPDATE
FROM MAKECAL 
	INNER JOIN --Get records without USERID
		(SELECT ROW_NUMBER() OVER(ORDER BY ID) AS RowNo, ID FROM MAKECAL WHERE USERID IS NULL) AS m ON MAKECAL.ID = m.ID 
	INNER JOIN --Get Users x10
		(SELECT ROW_NUMBER() OVER(ORDER BY n.N, u.USERID) AS RowNo, u.USERID 
			FROM [USER] AS u CROSS JOIN (SELECT ROW_NUMBER() OVER(ORDER BY object_id) AS N FROM sys.columns) AS n 
			WHERE n.N<=10) AS u 
		ON u.RowNo = m.RowNo

Open in new window

0
BernardGBaileyAuthor Commented:
Hi Rimvis,

Really appreciated you coming along to assist.  The MAKECAL table had no field for the USERNAME so I created it, with no data in it because there will be no data in it until after the Update.  Table USERS has USERNAME in it.  I've updated those fields in the code as below, but the output shows the columns but no records are displayed.  

Code as follows:

SELECT * --Check what will be updated
--UPDATE MAKECAL SET USERID = u.USERID --Uncomment this tu execute actual UPDATE
FROM MAKECAL 
	INNER JOIN --Get records without USERID
		(SELECT ROW_NUMBER() OVER(ORDER BY USERNAME) AS RowNo, USERNAME FROM MAKECAL WHERE USERNAME IS NULL) AS m ON MAKECAL.USERNAME = m.USERNAME 
	INNER JOIN --Get Users x10
		(SELECT ROW_NUMBER() OVER(ORDER BY n.N, u.USERNAME) AS RowNo, u.USERNAME
			FROM [USERS] AS u CROSS JOIN (SELECT ROW_NUMBER() OVER(ORDER BY object_id) AS N FROM sys.columns) AS n 
			WHERE n.N<=10) AS u 
		ON u.RowNo = m.RowNo

Open in new window

0
RimvisCommented:
Does MAKECAL.USERNAME  allows NULL? Or is it set to empty string? In this case, you should use SELECT ROW_NUMBER() OVER(ORDER BY USERNAME) AS RowNo, USERNAME FROM MAKECAL WHERE USERNAME = ''
0
The Five Tenets of the Most Secure Backup

Data loss can hit a business in any number of ways. In reality, companies should expect to lose data at some point. The challenge is having a plan to recover from such an event.

BernardGBaileyAuthor Commented:
Hi Rimvis,

MAKECAL.USERNAME is ALLOW NULLS

No change if I use the code above

Cheers
bernard
0
RimvisCommented:
Ok, do you get any output from this?
SELECT ROW_NUMBER() OVER(ORDER BY USERNAME) AS RowNo, USERNAME FROM MAKECAL WHERE USERNAME IS NULL
0
BernardGBaileyAuthor Commented:
Sorry Rimvis nothing still,  however there are 46 records in the users table and I'm trying to match 16 of them which have USERGROUP = 'ACTIVE'.  That is probablty the issue here.

Here is the code I have with the latest change as you suggested

SELECT * --Check what will be updated
--UPDATE MAKECAL SET USERID = u.USERID --Uncomment this tu execute actual UPDATE
FROM MAKECAL 
	INNER JOIN --Get records without USERID
		(SELECT ROW_NUMBER() OVER(ORDER BY USERNAME) AS RowNo, USERNAME FROM MAKECAL WHERE USERNAME IS NULL ) AS m ON MAKECAL.USERNAME = m.USERNAME
	INNER JOIN --Get Users x10
		(SELECT ROW_NUMBER() OVER(ORDER BY n.N, u.USERNAME) AS RowNo, u.USERNAME
			FROM [USERS] AS u CROSS JOIN (SELECT ROW_NUMBER() OVER(ORDER BY object_id) AS N FROM sys.columns) AS n 
			WHERE n.N<=10) AS u 
		ON u.RowNo = m.RowNo

Open in new window

0
RimvisCommented:
So this select returns no records, right?
SELECT * FROM MAKECAL WHERE USERNAME IS NULL
0
BernardGBaileyAuthor Commented:
Rimvis,

SELECT * FROM MAKECAL WHERE USERNAME IS NULL  returns all the records  (160)


SELECT * FROM MAKECAL WHERE USERNAME = '' returns no records.
0
RimvisCommented:
Here's the problem:
(SELECT ROW_NUMBER() OVER(ORDER BY USERNAME) AS RowNo, USERNAME FROM MAKECAL WHERE USERNAME IS NULL ) AS m ON MAKECAL.USERNAME = m.USERNAME

You are joining tables on USERNAME, which is empty. You should use primary key column of MAKECAL here. I assumed it is called ID. If you post your table structure, I could help with the exact statement.
0
BernardGBaileyAuthor Commented:
Rinvis,

There is no joining field; what I'm trying to do is populate the 160 records of the MAKECAL table with the 16 usernames from the USERS table for 10 MAKECAL records each.

Here are the MAKECAL fields

REATE TABLE dbo.MAKECAL (
  ACCOUNTNO varchar COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
  COMPANY varchar COLLATE SQL_Latin1_General_CP1_CI_AS,
  CONTACT varchar COLLATE SQL_Latin1_General_CP1_CI_AS,
  PHONE1 varchar COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
  EXT3 varchar COLLATE SQL_Latin1_General_CP1_CI_AS,
  ADDRESS1 varchar COLLATE SQL_Latin1_General_CP1_CI_AS,
  ADDRESS2 varchar COLLATE SQL_Latin1_General_CP1_CI_AS,
  ADDRESS3 varchar COLLATE SQL_Latin1_General_CP1_CI_AS,
  CITY varchar COLLATE SQL_Latin1_General_CP1_CI_AS,
  DEAR varchar COLLATE SQL_Latin1_General_CP1_CI_AS,
  SOURCE varchar COLLATE SQL_Latin1_General_CP1_CI_AS,
  KEY1 varchar COLLATE SQL_Latin1_General_CP1_CI_AS,
  KEY2 varchar COLLATE SQL_Latin1_General_CP1_CI_AS,
  KEY3 varchar COLLATE SQL_Latin1_General_CP1_CI_AS,
  KEY4 varchar COLLATE SQL_Latin1_General_CP1_CI_AS,
  KEY5 varchar COLLATE SQL_Latin1_General_CP1_CI_AS,
  USERNAME varchar COLLATE SQL_Latin1_General_CP1_CI_AS
);^

And the USERS fields

CREATE TABLE dbo.USERS (
  USERNAME varchar COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
  NAME varchar COLLATE SQL_Latin1_General_CP1_CI_AS,
  USERGROUP varchar COLLATE SQL_Latin1_General_CP1_CI_AS,
  INGROUPS varchar COLLATE SQL_Latin1_General_CP1_CI_AS,
  LASTIND datetime,
  LASTINT varchar COLLATE SQL_Latin1_General_CP1_CI_AS,
  LASTOUTD datetime,
  LASTOUTT varchar COLLATE SQL_Latin1_General_CP1_CI_AS,
  SOUNDEFFCT varchar COLLATE SQL_Latin1_General_CP1_CI_AS,
  USECURITY text COLLATE SQL_Latin1_General_CP1_CI_AS,
  OPTIONS varchar COLLATE SQL_Latin1_General_CP1_CI_AS,
  LASTUSER varchar COLLATE SQL_Latin1_General_CP1_CI_AS,
  LASTDATE datetime,
  LASTTIME varchar COLLATE SQL_Latin1_General_CP1_CI_AS,
  EMC_ACC_EMAILS text COLLATE SQL_Latin1_General_CP1_CI_AS,
  U_USERGROUP varchar COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
  recid varchar COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
);^

 Perhaps the best  idea is to pull all the 16 Users names where  USERGROUP = 'ACTIVE' into an array, then update the MAKECAL from the array, by stepping through 10 rows and then getting the next array name?

Could this work?

Your help is much appreciated
Cheers
Bernard
0
RimvisCommented:
Hello Bernard,

A table should ALWAYS have a primary key. You need a way to reference record in the table. For example, if USERNAME in table USERS is unique, it can be used to identity user record in the table. As for MAKECAL, I don't know. Is ACCOUNTNO unique in this table?
0
BernardGBaileyAuthor Commented:
Yes USERS.USERNAME is unique as is MAKECAL.ACCOUNTNO
0
RimvisCommented:
Try this:
SELECT * --Check what will be updated
--UPDATE MAKECAL SET USERID = u.USERID --Uncomment this tu execute actual UPDATE
FROM MAKECAL 
	INNER JOIN --Get records without USERID
			(SELECT ROW_NUMBER() OVER(ORDER BY ACCOUNTNO) AS RowNo, ACCOUNTNO FROM MAKECAL WHERE USERNAME IS NULL ) AS m 
		ON MAKECAL.ACCOUNTNO = m.ACCOUNTNO
	INNER JOIN --Get Users x10
		(SELECT ROW_NUMBER() OVER(ORDER BY n.N, u.USERNAME) AS RowNo, u.USERNAME
			FROM [USERS] AS u CROSS JOIN (SELECT ROW_NUMBER() OVER(ORDER BY object_id) AS N FROM sys.columns) AS n 
			WHERE n.N<=10) AS u 
		ON u.RowNo = m.RowNo
                                    

Open in new window

0
BernardGBaileyAuthor Commented:
Yes! That worked ok,  though I need to restrict the USERS table to where USERGROUPS = 'ACTIVE'.  Where will this go?
0
RimvisCommented:
Here:
SELECT * --Check what will be updated
--UPDATE MAKECAL SET USERID = u.USERID --Uncomment this tu execute actual UPDATE
FROM MAKECAL
      INNER JOIN --Get records without USERID
                  (SELECT ROW_NUMBER() OVER(ORDER BY ACCOUNTNO) AS RowNo, ACCOUNTNO FROM MAKECAL WHERE USERNAME IS NULL ) AS m
            ON MAKECAL.ACCOUNTNO = m.ACCOUNTNO
      INNER JOIN --Get Users x10
            (SELECT ROW_NUMBER() OVER(ORDER BY n.N, u.USERNAME) AS RowNo, u.USERNAME
                  FROM [USERS] AS u CROSS JOIN (SELECT ROW_NUMBER() OVER(ORDER BY object_id) AS N FROM sys.columns) AS n
                  WHERE u.USERGROUP='ACTIVE' AND n.N<=10) AS u
            ON u.RowNo = m.RowNo
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
BernardGBaileyAuthor Commented:
That is exactly what I wanted to achieve.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.