Solved

RoundRobin update of a table from a second table

Posted on 2012-04-02
16
309 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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
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

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

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 …
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

746 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

13 Experts available now in Live!

Get 1:1 Help Now