[ Resolved ] Updating 2 fields from subquery? - 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)

[ Resolved ] Updating 2 fields from subquery?

Author

Topic

DTFan
Yak Posting Veteran

52 Posts

Posted - 2006-10-11 : 12:33:20

Hey everyone. I hope this is something easy that I'm overlooking. I've been given a task to build a little stored procedure that will make sure some values always stay the same. Here's what I've got:


tblEmployee
empID (p/k, int)
empFName (v/c, 25)
empLName (v/c 25)
empNumber (v/c 10)
  ...

tblOffice
offID (p/k, int)
offName (v/c 35)
offAddress (v/c 100)
  ...

tblOfficeRoutes
orID (p/k int)
empID (f/k, int)
offID (f/k, int)
  ...

tblLastChecks
lcEmpNum (v/c 10)
currOffID (int)
lastOffID (int)

I didn't build this part but I have to work with it the way it is. So, here's what happens. You got employees who have specific offices they visit (some visit 3 or 4 and some only 1). So in tblOfficeRoutes, you'll have their foreign key to the tblEmployee table and the foreign key to the Offices that they visit. There is also a tblLastChecks table which (and I don't know why they did it this way) lists the Employee Number (not the ID but their number) and the last office they were at and the one that they are currently at.

The problem seems to be that even though employee 123 is only supposed to visit 1 office, sometimes they will have two different numbers in tblLastChecks. I've never seen it but I've been told it has happened and now they want a bandaid. I'm thinking the best approach (if this is possible) would be to have the stored procedure get a list of all empID's from tblOfficeRoutes who only have 1 office in their list, then it will update the tblLastChecks table and set BOTH currOffID and lastOffID to whatever the single offID field is (from tblOfficeRoutes) for that empID.

This is not my database and I can't "change" anything because it's hit by a lot of customers/employees/etc. So I've been tasked to create the bandaid stored procedure.

I've seen some code like this:


UPDATE P
SET People.LastName = Schools.[School Name] 
FROM Schools
     JOIN People AS P
          ON P.SomeColumn  = Schools.SomeColumn

but I'm not sure how that would work when there are 2 fields to be updated? Would that mean you've got to have 2 SETS (each with a subquery)? I've seen some examples of using "HAVING COUNT" so I think that would be part of it. But I can't figure out how to do this.

I tried:


select empID from tblOfficeRoutes GROUP BY empID HAVING COUNT(empID) = 1

but that returns employees who have more than one listing in tblOfficeRoutes.

As I'm sure you can tell from this crazy post I'm lost. I've done some work with SubQueries but Updating from a Select? Way beyond what I can figure out.

Any suggestions (other than setting a torch to the database and starting from scratch)?

Any and all help/suggestions/ideas is greatly appreciated.

DTFan <- banging head against wall next to desk

DTFan
Ever-hopeful programmer-in-training

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-10-11 : 13:52:33

Try this

UPDATE tblLastChecks SET currOffID = min(o.offID), lastOffID = min(o.offID)
FROM tblLastChecks
    INNER JOIN tblEmployee e ON e.empNumber = lcEmpNum
    INNER JOIN tblOfficeRoutes o ON o.empID = e.empID
    GROUP BY e.empID
    HAVING count(*) = 1

DTFan
Yak Posting Veteran

52 Posts

Posted - 2006-10-11 : 16:17:57

That gave me an error of something like not being allowed to have an aggregate in an update statement (something like that). Right now I'm sitting at someone elses computer waiting for a meeting to start so I don't have the code available right now. Once I get home I'll log back on and see if I did type something incorrectly.

Thanks for the suggestion and I'll let you know how it goes.

DTFan

DTFan
Ever-hopeful programmer-in-training

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-10-11 : 18:25:10

Sorry my mistake - that comes from not testing before posting

BTW - when you post it helps a lot if you can give working T-SQL table definitions rather than pseudocode 'cause then it's much easier for us to test

However - here is the correct query for what you want

UPDATE tblLastChecks 
SET currOffID = 
	(SELECT min(o.offID)
    FROM tblEmployee e
    INNER JOIN tblOfficeRoutes o ON o.empID = e.empID
    WHERE e.empNumber = lcEmpNum), 
	lastOffID = (SELECT min(o.offID)
    FROM tblEmployee e
    INNER JOIN tblOfficeRoutes o ON o.empID = e.empID
    WHERE e.empNumber = lcEmpNum)
WHERE 1 = (SELECT count(*)
    FROM tblEmployee e
    INNER JOIN tblOfficeRoutes o ON o.empID = e.empID
    WHERE e.empNumber = lcEmpNum)

DTFan
Yak Posting Veteran

52 Posts

Posted - 2006-10-12 : 12:49:57

quote:
Originally posted by snSQL
Sorry my mistake - that comes from not testing before posting
BTW - when you post it helps a lot if you can give working T-SQL table definitions rather than pseudocode 'cause then it's much easier for us to test

Sweet. That was perfect. It did exactly what I was hoping for.

As to the T-SQL stuff, you're right. I apologize for that. And I do know better (I've posted that before on other threads). No excuse ... my bad!

Thank you again for the quick and accurate solution. It has allowed me to write a stored proc that does exactly what the powers that be wanted done.

DTFan

DTFan
Ever-hopeful programmer-in-training

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources