Solved

RoundRobin update of a table from a second table

Posted on 2012-04-02
16
311 Views
Last Modified: 2012-04-04
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
0
Comment
Question by:BernardGBailey
  • 8
  • 8
16 Comments
 
LVL 19

Expert Comment

by:Rimvis
ID: 37799607
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
 

Author Comment

by:BernardGBailey
ID: 37799763
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
 
LVL 19

Expert Comment

by:Rimvis
ID: 37799782
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
 

Author Comment

by:BernardGBailey
ID: 37799820
Hi Rimvis,

MAKECAL.USERNAME is ALLOW NULLS

No change if I use the code above

Cheers
bernard
0
 
LVL 19

Expert Comment

by:Rimvis
ID: 37799856
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
 

Author Comment

by:BernardGBailey
ID: 37800155
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
 
LVL 19

Expert Comment

by:Rimvis
ID: 37800261
So this select returns no records, right?
SELECT * FROM MAKECAL WHERE USERNAME IS NULL
0
 

Author Comment

by:BernardGBailey
ID: 37803161
Rimvis,

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


SELECT * FROM MAKECAL WHERE USERNAME = '' returns no records.
0
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
LVL 19

Expert Comment

by:Rimvis
ID: 37804647
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
 

Author Comment

by:BernardGBailey
ID: 37804871
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
 
LVL 19

Expert Comment

by:Rimvis
ID: 37804891
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
 

Author Comment

by:BernardGBailey
ID: 37804958
Yes USERS.USERNAME is unique as is MAKECAL.ACCOUNTNO
0
 
LVL 19

Expert Comment

by:Rimvis
ID: 37804975
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
 

Author Comment

by:BernardGBailey
ID: 37805005
Yes! That worked ok,  though I need to restrict the USERS table to where USERGROUPS = 'ACTIVE'.  Where will this go?
0
 
LVL 19

Accepted Solution

by:
Rimvis earned 500 total points
ID: 37805422
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
 

Author Closing Comment

by:BernardGBailey
ID: 37808193
That is exactly what I wanted to achieve.
0

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now