SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Updating Records


Updating Records

Author
Message
netguykb
netguykb
SSC-Enthusiastic
SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)

Group: General Forum Members
Points: 135 Visits: 100
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
GSquared
GSquared
SSC Guru
SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)

Group: General Forum Members
Points: 56219 Visits: 9730
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
netguykb
netguykb
SSC-Enthusiastic
SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)

Group: General Forum Members
Points: 135 Visits: 100
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.
bitbucket-25253
bitbucket-25253
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15357 Visits: 25280
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search