Updating Multiple Records With Select Same Table? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Updating Multiple Records With Select Same Table?

Author

Topic

DTFan
Yak Posting Veteran

52 Posts

Posted - 2007-01-09 : 10:40:21

Hey everyone. This is a continuation of my post from below (where I was shown how to do the insert part). I'm trying something a little different here. Here is the d/b schema and the sample data (also loaded in the previous thread):


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)

I learned from the previous post how to add a new employee and automatically have her be assigned the same newsletters as whichever employee from the same company has the most newsletters (yes, I know there are logic problems but I'm just trying to learn right now).

OK. What I would like to do now is a little different. 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 (a little different from my previous post). 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 receive the same newsletters in the same format as one specified employee.

Step 1 is resolved from the previous post (it does exactly what I want but I just have to edit it a little so it uses an employeeID that I pass (and not one chosen from the count)).

I started working on Step 2 but keep hitting a problem. Here is the stored procedure that I've got so far:

CREATE PROCEDURE testproj_sp_SyncNewsLetterRecords
(

    @in_intEmpID INTEGER

)

AS

SET NOCOUNT ON

BEGIN

BEGIN TRAN

UPDATE      elet1
SET         elet1.nletID = [elet2.nletID],
            elet1.nlfID = [elet2.nlfID]
FROM        tblEmpLet elet1
INNER JOIN  (
                SELECT    elet2.nletID, elet2.nlfID
                FROM      tblEmpLet elet2
                WHERE     elet2.empID = @in_intEmpID
            )
            elet2 ON elet1.empID = elet2.empID

COMMIT

END

This keeps giving me an error saying:

quote:
Invalid column name 'empID'

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. Obviously I'm still stuck on the first step

Can anyone see what is causing the error? I don't understand why it would say that "empID" is not a valid column when it is clearly in the database? Also, 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.

DTFan
Ever-hopeful programmer-in-training

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-01-09 : 17:06:58

the error is because the derived table elet2 does not contains the column empID


UPDATE      elet1
SET         elet1.nletID = [elet2.nletID],
            elet1.nlfID = [elet2.nlfID]
FROM        tblEmpLet elet1
INNER JOIN  (
                SELECT    elet2.nletID, elet2.nlfID, elet2.empID
                FROM      tblEmpLet elet2
                WHERE     elet2.empID = @in_intEmpID
            )
            elet2 ON elet1.empID = elet2.empID

What is your logic with the above update ? It seems like it is updating the table same column with same value.

KH

DTFan
Yak Posting Veteran

52 Posts

Posted - 2007-01-10 : 09:31:20

quote:
Originally posted by khtan
What is your logic with the above update ? It seems like it is updating the table same column with same value.

It might be. I'm not really sure how to do what I'm trying to do (a LOT of trial and error). Basically I'm trying to have a stored procedure that will do several things (the 3 steps that I mentioned above). I was attempting, in the query above, to do an update for any existing employees in the tblEmpLet table (for the same company) to match the records for the employee who's ID I provide. There most likely is a logic problem but that's because the logic is coming from me

Thanks for the pointer about why I'm getting that error. I'm getting ready to start working on it again. Thank you again for the information.

DTFan
Ever-hopeful programmer-in-training

DTFan
Yak Posting Veteran

52 Posts

Posted - 2007-01-11 : 08:43:36

Yep, you were right. This procedure:


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.

DTFan
Ever-hopeful programmer-in-training

DTFan
Yak Posting Veteran

52 Posts

Posted - 2007-01-11 : 15:00:33

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 and the deletion of newsletters that aren't part of the designated employee, but this is something [Smile] 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

If anyone see's anything that could be done better/more efficiently, please don't hesitate to ask. Back to the SQL window for me.

DTFan
Ever-hopeful programmer-in-training

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources