Multiple records Update from Select on same table?
-
Hey everyone. Here is the d/b schema and the sample data (to hopefully make it easier to understand what I am babbling about
):**********
CREATE TABLE [tblCompany] (
[compID] int IDENTITY(1, 1) NOT NULL,
[compName] varchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS DEFAULT ('') NOT NULL
)
ON [PRIMARY]
GO
-- Structure for table tblEmpLet :
CREATE TABLE [tblEmpLet] (
[elID] bigint IDENTITY(1, 1) NOT NULL,
[empID] int NOT NULL,
[nletID] int NOT NULL,
[nlfID] int DEFAULT (1)
)
ON [PRIMARY]
GO
-- Structure for table tblEmployee :
CREATE TABLE [tblEmployee] (
[empID] int IDENTITY(1, 1) NOT NULL,
[compID] int NOT NULL,
[empFName] varchar(35) COLLATE SQL_Latin1_General_CP1_CI_AS DEFAULT ('') NOT NULL,
[empLName] varchar(35) COLLATE SQL_Latin1_General_CP1_CI_AS DEFAULT ('') NOT NULL,
[empEmail] varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS DEFAULT ('') NOT NULL
)
ON [PRIMARY]
GO
-- Structure for table tblNewsLetter :
CREATE TABLE [tblNewsLetter] (
[nletID] int IDENTITY(1, 1) NOT NULL,
[nletTitle] varchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS DEFAULT ('') NOT NULL,
[nletDesc] varchar(200) COLLATE SQL_Latin1_General_CP1_CI_AS DEFAULT ('') NOT NULL
)
ON [PRIMARY]
GO
-- Structure for table tblNLFormat :
CREATE TABLE [tblNLFormat] (
[nlfID] int IDENTITY(1, 1) NOT NULL,
[nlfType] varchar(4) COLLATE SQL_Latin1_General_CP1_CI_AS DEFAULT ('TEXT') NOT NULL
)
ON [PRIMARY]
GO
-- Data for table tblCompany (LIMIT 0,500)
INSERT INTO [tblCompany] ([compID], [compName])
VALUES
(1, 'Acme Widgets')
GO
INSERT INTO [tblCompany] ([compID], [compName])
VALUES
(2, 'Ajax Plumbing')
GO
INSERT INTO [tblCompany] ([compID], [compName])
VALUES
(3, 'Johnson Lawn Care')
GO
INSERT INTO [tblCompany] ([compID], [compName])
VALUES
(4, 'Wilsons Tire World')
GO
COMMIT
GO
-- Data for table tblEmpLet (LIMIT 0,500)
SET IDENTITY_INSERT [tblEmpLet] ON
GO
INSERT INTO [tblEmpLet] ([elID], [empID], [nletID], [nlfID])
VALUES
(1, 1, 4, 1)
GO
INSERT INTO [tblEmpLet] ([elID], [empID], [nletID], [nlfID])
VALUES
(2, 8, 6, 3)
GO
INSERT INTO [tblEmpLet] ([elID], [empID], [nletID], [nlfID])
VALUES
(3, 1, 5, 1)
GO
INSERT INTO [tblEmpLet] ([elID], [empID], [nletID], [nlfID])
VALUES
(4, 12, 2, 2)
GO
INSERT INTO [tblEmpLet] ([elID], [empID], [nletID], [nlfID])
VALUES
(5, 7, 7, 2)
GO
INSERT INTO [tblEmpLet] ([elID], [empID], [nletID], [nlfID])
VALUES
(6, 1, 5, 1)
GO
INSERT INTO [tblEmpLet] ([elID], [empID], [nletID], [nlfID])
VALUES
(7, 7, 1, 2)
GO
INSERT INTO [tblEmpLet] ([elID], [empID], [nletID], [nlfID])
VALUES
(8, 10, 10, 3)
GO
INSERT INTO [tblEmpLet] ([elID], [empID], [nletID], [nlfID])
VALUES
(9, 10, 6, 3)
GO
INSERT INTO [tblEmpLet] ([elID], [empID], [nletID], [nlfID])
VALUES
(10, 10, 2, 3)
GO
INSERT INTO [tblEmpLet] ([elID], [empID], [nletID], [nlfID])
VALUES
(11, 2, 5, 3)
GO
INSERT INTO [tblEmpLet] ([elID], [empID], [nletID], [nlfID])
VALUES
(12, 2, 8, 3)
GO
INSERT INTO [tblEmpLet] ([elID], [empID], [nletID], [nlfID])
VALUES
(13, 2, 9, 3)
GO
INSERT INTO [tblEmpLet] ([elID], [empID], [nletID], [nlfID])
VALUES
(14, 1, 3, 3)
GO
INSERT INTO [tblEmpLet] ([elID], [empID], [nletID], [nlfID])
VALUES
(15, 1, 9, 3)
GO
INSERT INTO [tblEmpLet] ([elID], [empID], [nletID], [nlfID])
VALUES
(16, 1, 12, 2)
GO
INSERT INTO [tblEmpLet] ([elID], [empID], [nletID], [nlfID])
VALUES
(17, 6, 5, 1)
GO
INSERT INTO [tblEmpLet] ([elID], [empID], [nletID], [nlfID])
VALUES
(18, 6, 10, 2)
GO
INSERT INTO [tblEmpLet] ([elID], [empID], [nletID], [nlfID])
VALUES
(19, 7, 3, 2)
GO
INSERT INTO [tblEmpLet] ([elID], [empID], [nletID], [nlfID])
VALUES
(20, 11, 1, 1)
GO
INSERT INTO [tblEmpLet] ([elID], [empID], [nletID], [nlfID])
VALUES
(21, 11, 3, 1)
GO
INSERT INTO [tblEmpLet] ([elID], [empID], [nletID], [nlfID])
VALUES
(22, 11, 8, 3)
GO
INSERT INTO [tblEmpLet] ([elID], [empID], [nletID], [nlfID])
VALUES
(23, 11, 12, 3)
GO
INSERT INTO [tblEmpLet] ([elID], [empID], [nletID], [nlfID])
VALUES
(24, 1, 11, 2)
GO
INSERT INTO [tblEmpLet] ([elID], [empID], [nletID], [nlfID])
VALUES
(25, 5, 5, 2)
GO
INSERT INTO [tblEmpLet] ([elID], [empID], [nletID], [nlfID])
VALUES
(26, 5, 12, 2)
GO
INSERT INTO [tblEmpLet] ([elID], [empID], [nletID], [nlfID])
VALUES
(27, 5, 3, 2)
GO
INSERT INTO [tblEmpLet] ([elID], [empID], [nletID], [nlfID])
VALUES
(28, 9, 5, 2)
GO
INSERT INTO [tblEmpLet] ([elID], [empID], [nletID], [nlfID])
VALUES
(29, 9, 9, 2)
GO
INSERT INTO [tblEmpLet] ([elID], [empID], [nletID], [nlfID])
VALUES
(30, 3, 3, 3)
GO
INSERT INTO [tblEmpLet] ([elID], [empID], [nletID], [nlfID])
VALUES
(31, 3, 8, 3)
GO
INSERT INTO [tblEmpLet] ([elID], [empID], [nletID], [nlfID])
VALUES
(32, 3, 1, 3)
GO
SET IDENTITY_INSERT [tblEmpLet] OFF
GO
COMMIT
GO
-- Data for table tblEmployee (LIMIT 0,500)
SET IDENTITY_INSERT [tblEmployee] ON
GO
INSERT INTO [tblEmployee] ([empID], [compID], [empFName], [empLName], [empEmail])
VALUES
(1, 3, 'Ali', 'Larter', 'ali@heroes.com')
GO
INSERT INTO [tblEmployee] ([empID], [compID], [empFName], [empLName], [empEmail])
VALUES
(2, 2, 'Jennifer', 'Morrison', 'jenm@house.com')
GO
INSERT INTO [tblEmployee] ([empID], [compID], [empFName], [empLName], [empEmail])
VALUES
(3, 2, 'Rhona', 'Mitra', 'rmitra@niptuck.com')
GO
INSERT INTO [tblEmployee] ([empID], [compID], [empFName], [empLName], [empEmail])
VALUES
(4, 1, 'Jordana', 'Brewster', 'jbrew@fastandfurious.net')
GO
INSERT INTO [tblEmployee] ([empID], [compID], [empFName], [empLName], [empEmail])
VALUES
(5, 3, 'Amy', 'Smart', 'amys@butterflyeffect.com')
GO
INSERT INTO [tblEmployee] ([empID], [compID], [empFName], [empLName], [empEmail])
VALUES
(6, 2, 'Keira', 'Knightley', 'keirak@potc3.com')
GO
INSERT INTO [tblEmployee] ([empID], [compID], [empFName], [empLName], [empEmail])
VALUES
(7, 1, 'Anna', 'Kournikova', 'annak@tennis.com')
GO
INSERT INTO [tblEmployee] ([empID], [compID], [empFName], [empLName], [empEmail])
VALUES
(8, 1, 'Mila', 'Kunis', 'milak@that70sshow.com')
GO
INSERT INTO [tblEmployee] ([empID], [compID], [empFName], [empLName], [empEmail])
VALUES
(9, 4, 'Sarah', 'Paulson', 'sarahp@studio60.net')
GO
INSERT INTO [tblEmployee] ([empID], [compID], [empFName], [empLName], [empEmail])
VALUES
(10, 2, 'Kelsey', 'Oldershaw', 'kelsey.oldershaw@ering.org')
GO
INSERT INTO [tblEmployee] ([empID], [compID], [empFName], [empLName], [empEmail])
VALUES
(11, 4, 'Sarah', 'Lancaster', 'sarahl@everwood.com')
GO
INSERT INTO [tblEmployee] ([empID], [compID], [empFName], [empLName], [empEmail])
VALUES
(12, 3, 'Evangeline', 'Lilly', 'elilly@lost.com')
GO
SET IDENTITY_INSERT [tblEmployee] OFF
GO
COMMIT
GO
-- Data for table tblNewsLetter (LIMIT 0,500)
SET IDENTITY_INSERT [tblNewsLetter] ON
GO
INSERT INTO [tblNewsLetter] ([nletID], [nletTitle], [nletDesc])
VALUES
(1, 'Hot Stock Tips', '')
GO
INSERT INTO [tblNewsLetter] ([nletID], [nletTitle], [nletDesc])
VALUES
(2, 'Lawn Care Basics', '')
GO
INSERT INTO [tblNewsLetter] ([nletID], [nletTitle], [nletDesc])
VALUES
(3, 'How To Change A Tire', '')
GO
INSERT INTO [tblNewsLetter] ([nletID], [nletTitle], [nletDesc])
VALUES
(4, '10 Ways To Lose Money', '')
GO
INSERT INTO [tblNewsLetter] ([nletID], [nletTitle], [nletDesc])
VALUES
(5, 'Every Which Way With Widgets', '')
GO
INSERT INTO [tblNewsLetter] ([nletID], [nletTitle], [nletDesc])
VALUES
(6, '10 Uses For Your Vanilla Ice CD', '')
GO
INSERT INTO [tblNewsLetter] ([nletID], [nletTitle], [nletDesc])
VALUES
(7, 'Hospital Rules For Doctors 101', '')
GO
INSERT INTO [tblNewsLetter] ([nletID], [nletTitle], [nletDesc])
VALUES
(8, 'Crab Grass And You - Its Not So Bad', '')
GO
INSERT INTO [tblNewsLetter] ([nletID], [nletTitle], [nletDesc])
VALUES
(9, 'Piping Hot News for Plumbers', '')
GO
INSERT INTO [tblNewsLetter] ([nletID], [nletTitle], [nletDesc])
VALUES
(10, 'Auto Tips From The Trade Show', '')
GO
INSERT INTO [tblNewsLetter] ([nletID], [nletTitle], [nletDesc])
VALUES
(11, 'Houseplant Tips For Everyone', '')
GO
INSERT INTO [tblNewsLetter] ([nletID], [nletTitle], [nletDesc])
VALUES
(12, 'Flowers versus Plants: Which Do You Want', '')
GO
INSERT INTO [tblNewsLetter] ([nletID], [nletTitle], [nletDesc])
VALUES
(13, 'Monthly Patterns For Quilts', '')
GO
SET IDENTITY_INSERT [tblNewsLetter] OFF
GO
COMMIT
GO
-- Data for table tblNLFormat (LIMIT 0,500)
SET IDENTITY_INSERT [tblNLFormat] ON
GO
INSERT INTO [tblNLFormat] ([nlfID], [nlfType])
VALUES
(1, 'TEXT')
GO
INSERT INTO [tblNLFormat] ([nlfID], [nlfType])
VALUES
(2, 'HTML')
GO
INSERT INTO [tblNLFormat] ([nlfID], [nlfType])
VALUES
(3, 'BOTH')
GO
SET IDENTITY_INSERT [tblNLFormat] OFF
GO
COMMIT
GO
-- Definition for indices :
ALTER TABLE [dbo].[tblCompany]
ADD CONSTRAINT [PK_tblCompany] PRIMARY KEY CLUSTERED ([compID])
ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblEmpLet]
ADD CONSTRAINT [PK_tblEmpLet] PRIMARY KEY CLUSTERED ([elID])
ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblEmployee]
ADD CONSTRAINT [PK_tblEmployee] PRIMARY KEY CLUSTERED ([empID])
ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblNewsLetter]
ADD CONSTRAINT [PK_tblNewsLetter] PRIMARY KEY CLUSTERED ([nletID])
ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblNLFormat]
ADD CONSTRAINT [PK_tblNLFormat] PRIMARY KEY CLUSTERED ([nlfID])
ON [PRIMARY]
GO
-- Definition for foreign keys %s :
ALTER TABLE [dbo].[tblEmpLet]
ADD CONSTRAINT [tblEmpLet_fk1] FOREIGN KEY ([empID])
REFERENCES [dbo].[tblEmployee] ([empID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[tblEmpLet]
ADD CONSTRAINT [tblEmpLet_fk2] FOREIGN KEY ([nletID])
REFERENCES [dbo].[tblNewsLetter] ([nletID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[tblEmpLet]
ADD CONSTRAINT [tblEmpLet_fk3] FOREIGN KEY ([nlfID])
REFERENCES [dbo].[tblNLFormat] ([nlfID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[tblEmployee]
ADD CONSTRAINT [tblEmployee_fk] FOREIGN KEY ([compID])
REFERENCES [dbo].[tblCompany] ([compID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
**********
So basically, you've got 4 Companies:
1 - Acme Widgets
2 - Ajax Plumbing
3 - Johnson Lawn Care
4 - Wilsons Tire World
Company #3 (Johnson Lawn Care) has 3 employees currently receiving newsletters:
Ali Larter (7 newsletters)
Amy Smart (3 newsletters)
Evangeline Lilly (1 newsletter)
OK. Lets say I'm adding 5 new employees to Johnson Lawn Care. What I want to do is have them added (without assigning newsletters) to the employee table. I've got that part working.
Here's what to me is the tricky part. I'll break it down into steps (although these do not necessarily have to be in the same order when it processes):
1) For all new employees, add them to tblEmpLet using the same fields as whichever employee I designate. I'll be passing @in_intEmpID parameter. So if I designate Ali Larters employeeID (1), then the 5 new employees would all get the same newsletters in the same format as her.
2) For any existing employees who get newsletters, if they receive the same newsletters as the designated employee, make sure it's in the same format (if it's not, update it so it is). If they don't, then add that newsletter to their list (in the same format).
3) For any existing employees who get newsletters, if they receive a newsletters that the designated employee does NOT receive, then delete that newsletter entry from tblEmpLet.
Basically I am just try to have a "sync" button to make sure all employees (from the same company as the employee I specify) receive the same newsletters in the same format as the one specified employee.
I figured that the best way to approach this was a 3 step process. First update the existing records, then add the new employees, then do the delete. Here is what I have for the "inserting" new employees into the table.
**********
CREATE PROCEDURE testproj_sp_AddNewEmployeeNewsletters
(
@in_intEmpID INTEGER
)
AS
SET NOCOUNT ON
BEGIN
BEGIN TRAN
INSERT INTO tblEmpLet
SELECT @in_intEmpID, [nletID], [nlfID]
FROM tblEmpLet
WHERE empID = @in_intEmpID
COMMIT
END
**********
Are my "3-steps" the best approach or is there a better way to do this?
Thank you for taking the time to read this and thank you in advance for any and all help that you can give. It is very much appreciated. And I apologize for the long post (hopefully it wasn't too confusing). Thanks again.
-
Well, this part didn't work. I tried running this procedure:
*****
-- @in_intEmpID is an input parameter I pass to the stored procedure
-- to indicate which employees newsletters I want to duplicate
UPDATE elet1
SET elet1.nletID = elet2.nletID,
elet1.nlfID = elet2.nlfID
FROM tblEmpLet elet1
INNER JOIN (
SELECT elID, empID, nletID, nlfID
FROM tblEmpLet
WHERE empID = @in_intEmpID
)
elet2 ON elet2.empID = elet1.empID
*****
only updated all of the tblEmpLet entries for the employee I specified. So instead of updating the other employees to match hers (empID = 1), all of her 7 were changed to the same nletID and nlfID (all 4, 1).
I know I can do this with multiple calls. I could run a stored procedure to return all of the designated employees newsletters, the loop through the recordset and call individual updates on a per employee basis. But that can't be the most efficient way to do it. I mean, 10 employees in a company and 5 newsletters to handle, without adding/deleting records we're still looking at 50 calls to the d/b.
Well, I picked up SQL For Mere Mortals so hopefully I'll find something in there. If I do I'll post it here. Any additional suggestions/idea's/etc will be greatly appreciated. And thanks in advance for any help you can give.
-
OK. Well, I've made some progress (still a long way to go). But I've got the "updating" part working (partially). This procedure will Update any existing employees who have the same newsletters. I still have to add in the part where it will Insert any of the designated employees newsletters for them, but this is something
I figured I would post it in case it can help somebody else. Also, if anyone see's a problem with it, please don't hesitate to let me know.**********
CREATE PROCEDURE admin_sp_SyncNewsLetters
(
@in_intEmpID INTEGER
)
AS
BEGIN
DECLARE @intCompID INTEGER
BEGIN TRAN
SET @intCompID = (SELECT compID FROM tblEmployee WHERE empID = @in_intEmpID)
UPDATE elet1
SET elet1.nletid = elet2.nletID,
elet1.nlfid = elet2.nlfid
FROM tblEmpLet elet1
INNER JOIN (
SELECT elid, empID, nletID, nlfid
FROM tblemplet
WHERE empID = @in_intEmpID
) elet2 ON elet2.nletID = elet1.nletID
WHERE elet1.empID IN (
SELECT empID
FROM tblEmployee
WHERE compID = @intCompID
)
COMMIT
END
**********
Back to the SQL window for me.
PS - is there anyway to format my message's so they are easier to read? Thanks.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply