udate problem

Question

udate problem

pat-261672

Hall of Fame

Points: 3325
More actions
December 4, 2008 at 8:25 pm

#128994

Hi all,
I am trying to update location for employee organization:
any employee that has organization field updates to the data that’s in their managers organization field
i have query but it updates all employee: please check if i am missing anything
UPDATE LOCATIONS
SET LOCATIONS.org_id = ( SELECT PERSON.uname
FROM PERSON
WHERE PERSON.emp_id = PERSON.mng_id)
WHERE uname EXISTS
( SELECT PERSON.uname
FROM PERSON
WHERE PERSON.emp_id = PERSON.mng_id);

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 399278 More actions · Answer 1

pat (12/4/2008)
Hi all,
I am trying to update location for employee organization:
any employee that has organization field updates to the data that’s in their managers organization field
i have query but it updates all employee: please check if i am missing anything
UPDATE LOCATIONS
SET LOCATIONS.org_id = ( SELECT PERSON.uname
FROM PERSON
WHERE PERSON.emp_id = PERSON.mng_id)
WHERE uname EXISTS
( SELECT PERSON.uname
FROM PERSON
WHERE PERSON.emp_id = PERSON.mng_id);

I'm a bit confused by the question and the query.

You're updating a column called org_id with a value from a column called uname. Based on that alone I'd say you've got an integer value getting updated from a string. Is that what you intended?

Also, you're comparing two fields within the person table like this

WHERE PERSON.emp_id = PERSON.mng_id

Are you really storing the same value in two columns on the same row?

Also, you're then using the uname in the locations table against all persons where the same two values are the same.

I'm confused or the structure is more than a bit off here. Can you post the structure as well as the query? That will help.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

ps. One Orange Chip Points: 29423 More actions · Answer 2

Also this statement looks confusing..

you're trying to update org_id in LOCATIONS table with uname ( i assume it's meant to store person's first name) from PERSON Table....

Pradeep Singh

pat-261672 Hall of Fame Points: 3325 More actions · Answer 3

sorry to make it confusion on the query:

Tables:

Person : emp_id, org_id,mgr_id,loc_id

manger : Mgr_id, Name

Location : Loc_id, disc

Organization : org_id, disc

so now the question is :

org_id for all empid belong to mgr_id need to be same as mgr_id's org_id such as

if org_id = 2 for Mgr_id = 4 all emp_id belong to or report to mrg_id = 4 their org_id needs to be updated to org_id = 2 equavivalent to mgr_id's org_id.

thanks

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

-- Set org_id to be same org_id as manager:

UPDATE p SET org_id = m.org_id

FROM Person p

INNER JOIN Person m ON m.emp_id = p.mgr_id

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Grant Fritchey SSC Guru Points: 399278 More actions · Answer 5

pat (12/5/2008)
sorry to make it confusion on the query:
Tables:
Person : emp_id, org_id,mgr_id,loc_id
manger : Mgr_id, Name
Location : Loc_id, disc
Organization : org_id, disc
so now the question is :
org_id for all empid belong to mgr_id need to be same as mgr_id's org_id such as
if org_id = 2 for Mgr_id = 4 all emp_id belong to or report to mrg_id = 4 their org_id needs to be updated to org_id = 2 equavivalent to mgr_id's org_id.
thanks

I am sorry, but I'm going to have to ask for more clarification.

How do I know which OrgId the manager belongs to? From your structure, I assume it's this:

SELECT p.Org_id

FROM Person AS p

JOIN Manager AS m

ON p.Mgr_id = m.Mgr_Id

BUT... How then do I tell which Manager is managing which person? Because, that would be same query, from what I can see. It can't be both. It looks like your structure is actually missing a table to connect people to managers. Something like this:

CREATE TABLE dbo.PersonManager

(Mgr_Id int NOT NULL

,Emp_Id int NOT NULL)

GO

ALTER TABLE dbo.PersonManager ADD CONSTRAINT

PK_PersonManager PRIMARY KEY CLUSTERED

(

Mgr_Id,

Emp_Id

) ON [PRIMARY]

GO

You'd need foriegn keys too. Then, if you put a PersonId column on the Mgr table, you can use that to identify which manager is a person and then get the list of manager to person relationships to identify the people that belong to a manager and are within his group.

Anyway, that's more along the lines of what I'd do. I still don't see how your structure works.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

pat-261672 Hall of Fame Points: 3325 More actions · Answer 6

yes there are FK as well like mgr_id like to manager table with mgr_id,

loc_id links with Location table,

and org_id likns organization table as well.

Grant Fritchey SSC Guru Points: 399278 More actions · Answer 7

I'm not so much worried about the FK's. How do I get the data out of the structure you have defined?

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning