Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Updating Records Expand / Collapse
Author
Message
Posted Thursday, April 15, 2010 2:34 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, May 11, 2011 5:27 AM
Points: 30, Visits: 96
I have 3 tables
Table 1 (SCOUTS)
This table has cub scout names and other information

Table 2 (PARENT_GUARDIAN)
This table has parent information.

Table 3 (xref_PG_SCT)
This Table Joins Parents with scouts using the unique id from both SCOUTS and PARENT_GUARDIAN, more of a cross refernce table. There could ultimately be a many to many relationship

Here are the create table scripts:
CREATE TABLE [dbo].[SCOUTS]  ( 
[SCT_ID] int IDENTITY(1,1) NOT NULL,
[SCT_FNAME] nvarchar(25) NOT NULL,
[SCT_LNAME] nvarchar(25) NOT NULL,
[SCT_DOB] datetime NULL,
[REF_SCT_RANK_ID] int NOT NULL,
[REF_DEN_ID] int NULL,
[SCT_STATUS] varchar(8) NULL CONSTRAINT [DF_SCOUTS_SCT_STATUS] DEFAULT ('ACTIVE'),
CONSTRAINT [PK_SCOUTS] PRIMARY KEY([SCT_ID])
)
GO

CREATE TABLE [dbo].[xref_PG_SCT] (
[PG_ID] int NOT NULL,
[SCT_ID] int NOT NULL
)
GO
ALTER TABLE [dbo].[xref_PG_SCT]
ADD CONSTRAINT [FK_xref_PG_SCT_SCOUTS]
FOREIGN KEY([SCT_ID])
REFERENCES [dbo].[SCOUTS]([SCT_ID])
ON DELETE CASCADE
ON UPDATE CASCADE
NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[xref_PG_SCT]
ADD CONSTRAINT [FK_xref_PG_SCT_PARENT_GUARDIAN]
FOREIGN KEY([PG_ID])
REFERENCES [dbo].[PARENT_GUARDIAN]([PG_ID])
ON DELETE CASCADE
ON UPDATE CASCADE
NOT FOR REPLICATION
GO

CREATE TABLE [dbo].[PARENT_GUARDIAN] (
[PG_ID] int IDENTITY(150,1) NOT NULL,
[REF_PG_RELAT_ID] int NULL,
[PG_FNAME] nvarchar(25) NOT NULL,
[PG_LNAME] nvarchar(25) NOT NULL,
[PG_ADDRESS] nvarchar(50) NULL,
[PG_CITY] nvarchar(25) NULL,
[PG_STATE] varchar(2) NULL,
[PG_ZIP] nvarchar(11) NULL,
[PG_PPHONE] nvarchar(12) NULL,
[PG_WPHONE] nvarchar(12) NULL,
[PG_EPHONE] nvarchar(12) NULL,
[PG_EMAILHOME] nvarchar(75) NULL,
[PG_EMAILWORK] nvarchar(75) NULL,
[PG_USERNAME] nvarchar(25) NULL,
[PG_PASSWORD] nvarchar(25) NULL,
[PG_STATUS] varchar(8) NOT NULL CONSTRAINT [DF__PARENT_GU__PG_ST__76619304] DEFAULT ('ACTIVE'),
[PG_INSERTDATE] datetime NOT NULL CONSTRAINT [DF__PARENT_GU__PG_IN__7755B73D] DEFAULT (convert(varchar,getdate(),101)),
[PG_LASTVISIT] nvarchar(25) NULL,
[PG_PRIMARY] varchar(1) NULL,
CONSTRAINT [PK_PARENT_GUARDIAN] PRIMARY KEY([PG_ID])
)
GO

Here is some code for populating these tables:

-- FIRST LETS ADD SOME NEW SCOUNTS INTO THE TABLE
INSERT INTO [dbo].[SCOUTS]([SCT_ID], [SCT_FNAME], [SCT_LNAME], [SCT_DOB], [REF_SCT_RANK_ID], [REF_DEN_ID], [SCT_STATUS])
VALUES(1, N'Joey', N'Smith', '', 5, 1, 'ACTIVE')
GO
INSERT INTO [dbo].[SCOUTS]([SCT_ID], [SCT_FNAME], [SCT_LNAME], [SCT_DOB], [REF_SCT_RANK_ID], [REF_DEN_ID], [SCT_STATUS])
VALUES(2, N'Billy', N'Corells', '', 5, 1, 'ACTIVE')
GO
INSERT INTO [dbo].[SCOUTS]([SCT_ID], [SCT_FNAME], [SCT_LNAME], [SCT_DOB], [REF_SCT_RANK_ID], [REF_DEN_ID], [SCT_STATUS])
VALUES(3, N'Dean', N'Corells', '', 1, 2, 'ACTIVE')
GO
INSERT INTO [dbo].[SCOUTS]([SCT_ID], [SCT_FNAME], [SCT_LNAME], [SCT_DOB], [REF_SCT_RANK_ID], [REF_DEN_ID], [SCT_STATUS])
VALUES(4, N'Frank', N'Galley', '', 5, 3, 'ACTIVE')
GO

-- NEXT LETS CREATE SOME PARENTS FOR EACH OF THE SCOUTS

INSERT INTO [dbo].[PARENT_GUARDIAN]([PG_ID], [REF_PG_RELAT_ID], [PG_FNAME], [PG_LNAME], [PG_ADDRESS], [PG_CITY], [PG_STATE], [PG_ZIP], [PG_STATUS])
VALUES(1, 0, N'Julie', N'Smith', N'345 East Main Street', N'Boomfield', 'NY', N'13245','ACTIVE')
GO
INSERT INTO [dbo].[PARENT_GUARDIAN]([PG_ID], [REF_PG_RELAT_ID], [PG_FNAME], [PG_LNAME], [PG_ADDRESS], [PG_CITY], [PG_STATE], [PG_ZIP], [PG_STATUS])
VALUES(2, 0, N'Dan', N'Smith', N'345 East Main Street', N'Boomfield', 'NY', N'13245','ACTIVE')
GO
INSERT INTO [dbo].[PARENT_GUARDIAN]([PG_ID], [REF_PG_RELAT_ID], [PG_FNAME], [PG_LNAME], [PG_ADDRESS], [PG_CITY], [PG_STATE], [PG_ZIP], [PG_STATUS])
VALUES(3, 0, N'David', N'Corells', N'4612 Blueberry Drive', N'Gallant', 'NY', N'23412','ACTIVE')
GO
INSERT INTO [dbo].[PARENT_GUARDIAN]([PG_ID], [REF_PG_RELAT_ID], [PG_FNAME], [PG_LNAME], [PG_ADDRESS], [PG_CITY], [PG_STATE], [PG_ZIP], [PG_STATUS])
VALUES(4, 0, N'Sandra', N'Corells', N'4612 Blueberry Drive', N'Gallant', 'NY', N'23412','ACTIVE')
GO
INSERT INTO [dbo].[PARENT_GUARDIAN]([PG_ID], [REF_PG_RELAT_ID], [PG_FNAME], [PG_LNAME], [PG_ADDRESS], [PG_CITY], [PG_STATE], [PG_ZIP], [PG_STATUS])
VALUES(5, 0, N'Bob', N'Galley', N'89 South Church Street', N'Gallant', 'NY', N'23412','ACTIVE')
GO
INSERT INTO [dbo].[PARENT_GUARDIAN]([PG_ID], [REF_PG_RELAT_ID], [PG_FNAME], [PG_LNAME], [PG_ADDRESS], [PG_CITY], [PG_STATE], [PG_ZIP], [PG_STATUS])
VALUES(6, 0, N'Barbara', N'Galley', N'89 South Church Street', N'Gallant', 'NY', N'23412','ACTIVE')
GO

-- NOW WE ARE GOOING TO JOIN EACH SCOUT TO EACH PARENT USING OUR CROSS REFERENCE TABLE
INSERT INTO [dbo].[xref_PG_SCT]([PG_ID], [SCT_ID])
VALUES(1, 1)
GO
INSERT INTO [dbo].[xref_PG_SCT]([PG_ID], [SCT_ID])
VALUES(2, 1)
GO
INSERT INTO [dbo].[xref_PG_SCT]([PG_ID], [SCT_ID])
VALUES(3, 2)
GO
INSERT INTO [dbo].[xref_PG_SCT]([PG_ID], [SCT_ID])
VALUES(4, 2)
GO
INSERT INTO [dbo].[xref_PG_SCT]([PG_ID], [SCT_ID])
VALUES(3, 3)
GO
INSERT INTO [dbo].[xref_PG_SCT]([PG_ID], [SCT_ID])
VALUES(4, 3)
GO
INSERT INTO [dbo].[xref_PG_SCT]([PG_ID], [SCT_ID])
VALUES(5, 4)
GO
INSERT INTO [dbo].[xref_PG_SCT]([PG_ID], [SCT_ID])
VALUES(6, 4)
GO



Now heres what I need to update.
1) Set all scouts SCT_STATUS to 'INACTIVE' WHERE REF_SCT_RANK_ID = 5
2) Now I need to update the [PG_STATUS] to 'INACTIVE' only if there are no other scouts associated to those Parents where the scouts status was just updated based on step one

So for example in our data
All Parents other than David and Sandra Corells will have a PG_STATUS of INACTIVE. This is because they still have a scout where REF_SCT_RANK_ID was not = to 5 so there PG_STATUS must still remain active
Post #904391
Posted Thursday, April 15, 2010 3:12 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Monday, April 14, 2014 1:34 PM
Points: 15,442, Visits: 9,588
Can you take the PG_STATUS out of the database and just calculate it at runtime?

Join the three tables together, where the scout status is not equal to 5, and select the parent/guardian data from that. Don't store the value, just calculate it when you need it. Will that work?


- 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
Post #904427
Posted Friday, April 16, 2010 12:19 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, May 11, 2011 5:27 AM
Points: 30, Visits: 96
This really not an option since I am doing a bulk update. And because the status column has been used in so many procedures and other runtime components the rework would take way to much time. I have created a simple update query however it does not take into account the most critical aspect of the update which is to NOT change the status to INACTIVE for those parents where they have another child still in scouts. I only want to change the status if they have one scout associated to them.
Post #905106
Posted Friday, April 16, 2010 3:43 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 6:52 PM
Points: 5,472, Visits: 23,534
This is, I am sure NOT the best way, but since CTEs
can not be used in SQL 2000 the best I could come up
with is a 3 step approach.

/*This sets your parents status with 
multiple children one of which is still ACTIVE and
one of which is INACTIVE , to INACTIVE not what
is wanted but */

UPDATE [PARENT_GUARDIAN] SET PG_STATUS = 'INACTIVE'
WHERE EXISTS(SELECT p.PG_ID,s.SCT_STATUS,s.SCT_ID
FROM [PARENT_GUARDIAN] p
JOIN [dbo].[xref_PG_SCT] x
ON p.PG_ID = x.PG_ID
JOIN SCOUTS s ON
s.SCT_ID = x.SCT_ID WHERE s.SCT_STATUS = 'INACTIVE')

/* We then find and place into a temporarty table
those scouts that are still ACTIVE */

SELECT p.PG_ID
INTO #T
FROM [PARENT_GUARDIAN] p
JOIN [dbo].[xref_PG_SCT] x
ON p.PG_ID = x.PG_ID
JOIN SCOUTS s ON
s.SCT_ID = x.SCT_ID WHERE s.SCT_STATUS = 'ACTIVE'

/* Next we go back and set those parents to ACTIVE */

UPDATE [PARENT_GUARDIAN] SET PG_STATUS = 'ACTIVE'
FROM #T WHERE #T.PG_ID = PARENT_GUARDIAN.PG_ID

/* Check did we obtain the desired results

PG_LName PG_STATUS
Smith INACTIVE
Smith INACTIVE
Corells ACTIVE
Corells ACTIVE
Galley INACTIVE
Galley INACTIVE */

DROP TABLE #T

A brute force approach - maybe some one can refine it ..


If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read

Before posting a performance problem please read
Post #905248
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse