March 28, 2011 at 6:50 pm
I have 3 tables. One table contains the names of my cubscouts. Each row is a different scout and rank. Table 2 contains the names of my parents. Each row is a different parent. I.E. row 1 could be the father of scout 1 and row two could be the mother of scout one. Table 3 is a cross reference which associates parents to each of the their children. Some families have two scouts and other familys may only have 1 scout. At the end of the year I need to graduate all scouts moving onto boy scouts by simply updating the scout status to inactive where there rank is 5. At that same time I also want to update the parent table setting each parent's status to inactive only if they have one scout moving up in rank. The problem becomes: I do not want to update parents to INACTIVE if they have another scout who might be in a lower rank. In those situations its important to keep the parents status to ACTIVE. I need a scripts that will bulk update SCOUTS Table and PARENTS table based on this logic. I have included example scripts below for creating all 3 tables and inserting some data. Only SCOUTS where REF_SCT_RANK_ID = 5 should get STATUS update to 'INACTIVE'. Only those parents who have one scout with REF_SCT_RANK_ID = 5 will receive the STATUS update of 'INACTIVE'.
Any help would help... Thank you!!!!
/* FIRST TABLE STORES SCOUT INFORMATION */
CREATE TABLE [SCOUTS_TEMP] (
[SCT_ID] [int] IDENTITY (1, 1) NOT NULL ,
[SCT_FNAME] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[SCT_LNAME] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[REF_SCT_RANK_ID] [int] NOT NULL ,
[SCT_STATUS] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [PK_SCOUTS_TEMP] PRIMARY KEY CLUSTERED
(
[SCT_ID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
/* SECOND TABLE STORES EACH PARENT IN SEPERATE ROWS I.E. ROW 1 FATHER, ROW 2 MOTHER ETC...*/
CREATE TABLE [PARENT_GUARDIAN_TEMP] (
[PG_ID] [int] IDENTITY (1, 1) NOT NULL ,
[PG_FNAME] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[PG_LNAME] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[PG_STATUS] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
/* THIRD TABLE CROSS REFERENCES EACH SCOUT TO EACH PARENT */
CREATE TABLE [xref_PG_SCT_TEMP] (
[PG_ID] [int] NOT NULL ,
[SCT_ID] [int] NOT NULL
)
GO
/* LETS ENTER SOME DATA - FAMILY ONE - TWO PARENTS WITH TWO BOYS*/
INSERT INTO [pack262].[iis_writer].[SCOUTS_TEMP]([SCT_FNAME], [SCT_LNAME], [REF_SCT_RANK_ID], [SCT_STATUS])
VALUES('Billy','Jones','5','ACTIVE')
INSERT INTO [pack262].[iis_writer].[SCOUTS_TEMP]([SCT_FNAME], [SCT_LNAME], [REF_SCT_RANK_ID], [SCT_STATUS])
VALUES('David','Jones','4','ACTIVE')
INSERT INTO [pack262].[iis_writer].[PARENT_GUARDIAN_TEMP]([PG_FNAME], [PG_LNAME], [PG_STATUS])
VALUES('Frank','Jones','ACTIVE')
INSERT INTO [pack262].[iis_writer].[PARENT_GUARDIAN_TEMP]([PG_FNAME], [PG_LNAME], [PG_STATUS])
VALUES('Debe','Jones','ACTIVE')
INSERT INTO [pack262].[iis_writer].[xref_PG_SCT_TEMP]([PG_ID], [SCT_ID])
VALUES(1,1)
INSERT INTO [pack262].[iis_writer].[xref_PG_SCT_TEMP]([PG_ID], [SCT_ID])
VALUES(1,2)
INSERT INTO [pack262].[iis_writer].[xref_PG_SCT_TEMP]([PG_ID], [SCT_ID])
VALUES(2,1)
INSERT INTO [pack262].[iis_writer].[xref_PG_SCT_TEMP]([PG_ID], [SCT_ID])
VALUES(2,2)
/* LETS ENTER SOME DATA - FAMILY TWO - TWO PARENTS WITH ONE BOY*/
INSERT INTO [pack262].[iis_writer].[SCOUTS_TEMP]([SCT_FNAME], [SCT_LNAME], [REF_SCT_RANK_ID], [SCT_STATUS])
VALUES('Michael','Smith','5','ACTIVE')
INSERT INTO [pack262].[iis_writer].[PARENT_GUARDIAN_TEMP]([PG_FNAME], [PG_LNAME], [PG_STATUS])
VALUES('Chris','Smith','ACTIVE')
INSERT INTO [pack262].[iis_writer].[PARENT_GUARDIAN_TEMP]([PG_FNAME], [PG_LNAME], [PG_STATUS])
VALUES('Mary','Smith','ACTIVE')
INSERT INTO [pack262].[iis_writer].[xref_PG_SCT_TEMP]([PG_ID], [SCT_ID])
VALUES(3,3)
INSERT INTO [pack262].[iis_writer].[xref_PG_SCT_TEMP]([PG_ID], [SCT_ID])
VALUES(4,3)
/* LETS ENTER SOME DATA - FAMILY THREE - ONE PARENT WITH TWO BOYS*/
INSERT INTO [pack262].[iis_writer].[SCOUTS_TEMP]([SCT_FNAME], [SCT_LNAME], [REF_SCT_RANK_ID], [SCT_STATUS])
VALUES('Robert','Brady','5','ACTIVE')
INSERT INTO [pack262].[iis_writer].[SCOUTS_TEMP]([SCT_FNAME], [SCT_LNAME], [REF_SCT_RANK_ID], [SCT_STATUS])
VALUES('William','Brady','2','ACTIVE')
INSERT INTO [pack262].[iis_writer].[PARENT_GUARDIAN_TEMP]([PG_FNAME], [PG_LNAME], [PG_STATUS])
VALUES('Kyle','Brady','ACTIVE')
INSERT INTO [pack262].[iis_writer].[xref_PG_SCT_TEMP]([PG_ID], [SCT_ID])
VALUES(5,4)
INSERT INTO [pack262].[iis_writer].[xref_PG_SCT_TEMP]([PG_ID], [SCT_ID])
VALUES(5,5)
March 28, 2011 at 7:24 pm
Original corrected by Jeff Moden ... so my errors removed so as not to confuse others who may read this ....
March 28, 2011 at 7:52 pm
bitbucket-25253 (3/28/2011)
Thanks for the table definitions and sample data. However:1. You posted your question to a SQL Server 2000 forum and the insert into statements such as
INSERT INTO [pack262].[iis_writer].[PARENT_GUARDIAN_TEMP]([PG_FNAME], [PG_LNAME], [PG_STATUS])
VALUES('Debe','Jones','ACTIVE')
are using a format that is NOT valid in SQL 2000
Ohhh... careful, Ron... that's actually just fine in SQL Server 2000. Nope, I'm not making an educated guess either. BOL 2000 doesn't have it in the syntax listing for INSERT but "tablename" is actually any valid 4, 3, 2, or 1 part table name. If it weren't possible, the use of Linked Servers on the receiving end of INSERTs would be a really big problem.
To make doubly sure, I tested the following code on my local instance of SQL Server 2000 and it worked just like expected.
USE TempDB
GO
CREATE TABLE dbo.DODAH ([Whatever] VARCHAR(50))
INSERT INTO [TempDB].[dbo].[DODAH]([Whatever])
VALUES ('A test string')
--Jeff Moden
Change is inevitable... Change for the better is not.
March 28, 2011 at 7:59 pm
Hey folks...
NetGuyKB did a pretty good job of posting table and data creation scripts. The only thing he forgot was that we don't have either the DB name or the schema name he posted. If you do a Search'n'Replace of
[pack262].[iis_writer].
... to just ...
dbo.
... or ...
tempdb.dbo.
... his code should work just fine. Him being a Pack Leader, I'd jump right in and help except I have a very, very early appointment at work tomorrow and I have to hit the hay.
If anyone can help this fellow out tonight, being a former Pack Leader myself, I'd really appreciate it.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 28, 2011 at 8:18 pm
The SQL statements were all created in Query Analyzer (Script Object to clipboard as - right mouse) which all should be compatible to 2000. I only own SQL 2000. As far as the Schema Goes:
DB Name: pack262 is what I have however I have changed to tempdb
Username: DBO
Yes I am a cubmaster and I have spent the last 3 years making modifications to the site which is completely DB driven . Now the time has come time to finish those dreaded Admin pages that I have been putting off for so long in an attempt to make it real easy for anyone to perform those things that I would typically do manually on the back end. grrrr lots of work
I have made the changes to the script below:
/* FIRST TABLE STORES SCOUT INFORMATION */
CREATE TABLE [SCOUTS_TEMP] (
[SCT_ID] [int] IDENTITY (1, 1) NOT NULL ,
[SCT_FNAME] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[SCT_LNAME] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[REF_SCT_RANK_ID] [int] NOT NULL ,
[SCT_STATUS] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [PK_SCOUTS_TEMP] PRIMARY KEY CLUSTERED
(
[SCT_ID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
/* SECOND TABLE STORES EACH PARENT IN SEPERATE ROWS I.E. ROW 1 FATHER, ROW 2 MOTHER ETC...*/
CREATE TABLE [PARENT_GUARDIAN_TEMP] (
[PG_ID] [int] IDENTITY (1, 1) NOT NULL ,
[PG_FNAME] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[PG_LNAME] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[PG_STATUS] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
/* THIRD TABLE CROSS REFERENCES EACH SCOUT TO EACH PARENT */
CREATE TABLE [xref_PG_SCT_TEMP] (
[PG_ID] [int] NOT NULL ,
[SCT_ID] [int] NOT NULL
)
GO
/* LETS ENTER SOME DATA - FAMILY ONE - TWO PARENTS WITH TWO BOYS*/
INSERT INTO [tempdb].[dbo].[SCOUTS_TEMP]([SCT_FNAME], [SCT_LNAME], [REF_SCT_RANK_ID], [SCT_STATUS])
VALUES('Billy','Jones','5','ACTIVE')
INSERT INTO [tempdb].[dbo].[SCOUTS_TEMP]([SCT_FNAME], [SCT_LNAME], [REF_SCT_RANK_ID], [SCT_STATUS])
VALUES('David','Jones','4','ACTIVE')
INSERT INTO [tempdb].[dbo].[PARENT_GUARDIAN_TEMP]([PG_FNAME], [PG_LNAME], [PG_STATUS])
VALUES('Frank','Jones','ACTIVE')
INSERT INTO [tempdb].[dbo].[PARENT_GUARDIAN_TEMP]([PG_FNAME], [PG_LNAME], [PG_STATUS])
VALUES('Debe','Jones','ACTIVE')
INSERT INTO [tempdb].[dbo].[xref_PG_SCT_TEMP]([PG_ID], [SCT_ID])
VALUES(1,1)
INSERT INTO [tempdb].[dbo].[xref_PG_SCT_TEMP]([PG_ID], [SCT_ID])
VALUES(1,2)
INSERT INTO [tempdb].[dbo].[xref_PG_SCT_TEMP]([PG_ID], [SCT_ID])
VALUES(2,1)
INSERT INTO [tempdb].[dbo].[xref_PG_SCT_TEMP]([PG_ID], [SCT_ID])
VALUES(2,2)
/* LETS ENTER SOME DATA - FAMILY TWO - TWO PARENTS WITH ONE BOY*/
INSERT INTO [tempdb].[dbo].[SCOUTS_TEMP]([SCT_FNAME], [SCT_LNAME], [REF_SCT_RANK_ID], [SCT_STATUS])
VALUES('Michael','Smith','5','ACTIVE')
INSERT INTO [tempdb].[dbo].[PARENT_GUARDIAN_TEMP]([PG_FNAME], [PG_LNAME], [PG_STATUS])
VALUES('Chris','Smith','ACTIVE')
INSERT INTO [tempdb].[dbo].[PARENT_GUARDIAN_TEMP]([PG_FNAME], [PG_LNAME], [PG_STATUS])
VALUES('Mary','Smith','ACTIVE')
INSERT INTO [tempdb].[dbo].[xref_PG_SCT_TEMP]([PG_ID], [SCT_ID])
VALUES(3,3)
INSERT INTO [tempdb].[dbo].[xref_PG_SCT_TEMP]([PG_ID], [SCT_ID])
VALUES(4,3)
/* LETS ENTER SOME DATA - FAMILY THREE - ONE PARENT WITH TWO BOYS*/
INSERT INTO [tempdb].[dbo].[SCOUTS_TEMP]([SCT_FNAME], [SCT_LNAME], [REF_SCT_RANK_ID], [SCT_STATUS])
VALUES('Robert','Brady','5','ACTIVE')
INSERT INTO [tempdb].[dbo].[SCOUTS_TEMP]([SCT_FNAME], [SCT_LNAME], [REF_SCT_RANK_ID], [SCT_STATUS])
VALUES('William','Brady','2','ACTIVE')
INSERT INTO [tempdb].[dbo].[PARENT_GUARDIAN_TEMP]([PG_FNAME], [PG_LNAME], [PG_STATUS])
VALUES('Kyle','Brady','ACTIVE')
INSERT INTO [tempdb].[dbo].[xref_PG_SCT_TEMP]([PG_ID], [SCT_ID])
VALUES(5,4)
INSERT INTO [tempdb].[dbo].[xref_PG_SCT_TEMP]([PG_ID], [SCT_ID])
VALUES(5,5)
March 29, 2011 at 1:10 am
First shot:
( created a SSC_LVL80 db to mimic sql2000 behaviour )
Only those parents who have one scout with REF_SCT_RANK_ID = 5 will receive the STATUS update of 'INACTIVE'
Is the "one" in this line a strict number or are you meaning "a scout" ?
Please elaborate.
/* show starting data */
Select *
from dbo.SCOUTS_TEMP S
left join dbo.xref_PG_SCT_TEMP x
on x.SCT_ID = S.SCT_ID
left join dbo.PARENT_GUARDIAN_TEMP P
on p.PG_ID = x.PG_ID
order by S.SCT_ID, P.PG_ID ;
/* encapsulate in a single transaction */
begin tran
/* updating parents if no lower ranked scouts exist */
update P
set PG_STATUS = 'INACTIVE'
from dbo.PARENT_GUARDIAN_TEMP P
inner join dbo.xref_PG_SCT_TEMP x
on p.PG_ID = x.PG_ID
inner join dbo.SCOUTS_TEMP S
on S.SCT_ID = X.SCT_ID
and S.REF_SCT_RANK_ID = 5
Where not exists ( Select *
from dbo.PARENT_GUARDIAN_TEMP P1
inner join dbo.xref_PG_SCT_TEMP x1
on p1.PG_ID = x1.PG_ID
inner join dbo.SCOUTS_TEMP S1
on S1.SCT_ID = x1.SCT_ID
and S1.REF_SCT_RANK_ID <> 5
where P1.PG_ID = P.PG_ID )
/* updating scouts rank 5 to inactive if they are still active */
update dbo.SCOUTS_TEMP
set SCT_STATUS = 'INACTIVE'
where REF_SCT_RANK_ID = 5
and SCT_STATUS = 'ACTIVE'
/* show results after operation */
Select *
from dbo.SCOUTS_TEMP S
left join dbo.xref_PG_SCT_TEMP x
on x.SCT_ID = S.SCT_ID
left join dbo.PARENT_GUARDIAN_TEMP P
on p.PG_ID = x.PG_ID
order by S.SCT_ID, P.PG_ID ;
if @@trancount > 0
begin
/* using rollback to not break your current TEST data */
rollback tran
/* commit tran <- to be used with your effective solution. */
end
Remarks:
- double check your tables are indexed for their FK columns !
- your xref table doesn't have a PK. I would put one on it to avoid doubles !
- make sure you have your FK declared to avoid invalid data in the related columns !
- If your number affected rows is small compared to your total set, maybe toss in a temptb and first select the affected scouts and join with this temptb.
- maybe even create an index for column combination REF_SCT_RANK_ID, SCT_STATUS to support this operation
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
March 29, 2011 at 2:10 am
Or for /* updating parents if no lower ranked scouts exist */
UPDATE g
SET PG_STATUS='INACTIVE'
FROM [PARENT_GUARDIAN_TEMP] g
JOIN (SELECT g.PG_ID
FROM [PARENT_GUARDIAN_TEMP] g
JOIN [xref_PG_SCT_TEMP] x ON x.PG_ID=g.PG_ID
JOIN [SCOUTS_TEMP] s ON s.SCT_ID=x.SCT_ID
WHERE g.PG_STATUS='ACTIVE'
GROUP BY g.PG_ID
HAVING SUM(SIGN(REF_SCT_RANK_ID % 5))=0) a ON a.PG_ID=g.PG_ID
Far away is close at hand in the images of elsewhere.
Anon.
March 29, 2011 at 5:58 am
Yes you are correct. The primary point is: all scouts who have REF_SCT_RANK_ID = 5 must be updated as INACTIVE in the STATUS column. However Parent STATUS will be based on the following: If they only have one scout where his REF_SCT_RANK_ID = 5 then both parents need an UPDATE STATUS of INACTIVE. If they have two scouts associated to them and one scout REF_SCT_RANK_ID = 5 and another is REF_SCT_RANK_ID <= 4 then each parents STATUS remains ACTIVE
March 29, 2011 at 6:22 am
did the provided script match your question ?
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
March 29, 2011 at 6:35 am
ALZDBA ... Thank you this looks very clean. I have a few additional questions for you specific to your comments.
Remarks:
- double check your tables are indexed for their FK columns !
I agree. My original tables are much more complex in nature. For posting puposes only I simplified them
- your xref table doesn't have a PK. I would put one on it to avoid doubles !
When you say doubles im not sure what you are referring to. For example the only two values for each row are PG_ID and SCT_ID. i.e. 1,1 and 1,2 are examples of one parent and two scouts. 2,1 and 2,2 are examples of second parent to the same two scouts in the first example i.e. mom and dad . My application allows for Aunts Uncles, Grandparents and any combination. It also prohibits you from entering 1,1 or any existing relationship more than once. In past structures I have actually placed the Primary Key as both the PG_ID AND SCT_ID to avoid duplication.
- make sure you have your FK declared to avoid invalid data in the related columns !
I agree. My original tables are much more complex in nature. For posting puposes only I simplified them
- If your number affected rows is small compared to your total set, maybe toss in a temptb and first select the affected scouts and join with this temptb.
Not a bad idea and one that I would that I would defintately use for managing larger sets of updates. Typically there are only around 7 scouts once a year needing to be updated and another 10 or parents also needing to be updated.
- maybe even create an index for column combination REF_SCT_RANK_ID, SCT_STATUS to support this operation
March 29, 2011 at 6:39 am
I would avoid the update to the Parents table completely, and drop that column in the table. It would be easier to query the Parents table and calculate status based on child statuses in the query, instead of in the table.
If that's not an option:
UPDATE dbo.SCOUTS_TEMP
SET SCT_STATUS = 'INACTIVE'
WHERE SCT_ID = 2;
UPDATE dbo.PARENT_GUARDIAN_TEMP
SET PG_STATUS = 'INACTIVE'
WHERE PG_STATUS = 'ACTIVE'
AND NOT EXISTS
(SELECT 1
FROM dbo.xref_PG_SCT_TEMP
INNER JOIN dbo.SCOUTS_TEMP
ON dbo.xref_PG_SCT_TEMP.SCT_ID = dbo.SCOUTS_TEMP.SCT_ID
WHERE SCT_STATUS = 'ACTIVE'
AND dbo.xref_PG_SCT_TEMP.PG_ID = dbo.PARENT_GUARDIAN_TEMP.PG_ID);
You can use an input parameter where I have a 2, in order to turn this into a stored procedure.
If your actual data is much larger than this, you might get a performance improvement by adding a piece to the Where clause that limits it to only updating parents of the scout that you're currently updating. But that will only matter if you have tens or hundreds of thousands of parents, or are running this from a seriously low-end computer.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 29, 2011 at 7:47 am
GSquared (3/29/2011)
...If your actual data is much larger than this, you might get a performance improvement by adding a piece to the Where clause that limits it to only updating parents of the scout that you're currently updating. But that will only matter if you have tens or hundreds of thousands of parents, or are running this from a seriously low-end computer.
That's the exact reason why I did put the update of the parents before the update of the scouts, using the join with only scouts to be affected by the operation.
Best is to TEST the alternatives and go with the one that performs best.
"set statistics io, time on" can provide interesting indicative information with regards the effords the engine needs to do to fulfill your request as does "include actual execution plan" info.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
March 29, 2011 at 8:19 am
Both Points are well taken and in some cases I may want to run on an individual scout. There would be times where a scout may want to drop out all together. In the past I simply run two queries on the back end to make inactive on the scout and if need be on the parents of the scout. Thanks again guys I believe I will definitely be using a hybrid approach.
March 29, 2011 at 8:11 pm
Johan, Ron, Gus, and David... thanks for jumping in on this one. 🙂
--Jeff Moden
Change is inevitable... Change for the better is not.
March 29, 2011 at 10:03 pm
Jeff Moden (3/29/2011)
Johan, Ron, Gus, and David... thanks for jumping in on this one. 🙂
Jeff - no problem. I spent 6 years as a scout master for a troop with 60 boys (max membership). In those 6 years had 3 boys make Eagle Scout .. So the ops request for guidance struck a chord in my heart. Hope making his life easier will keep him as a cub scout leader for many more years.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply