Subquery In Update Statement - SQL Server Forums

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)

Subquery In Update Statement

Author

Topic

mahesh_bote
Constraint Violating Yak Guru

298 Posts

Posted - 2006-11-27 : 01:34:04

Hi,

can anybody tell me how to write sub query in Update statment with syntax?

thanks in advance,
Mahesh

mahesh_bote
Constraint Violating Yak Guru

298 Posts

Posted - 2006-11-27 : 01:49:59

or whats wrong with this query

Update
P_USER_MSTR
Set
P_User_Active = 'N'
,P_User_Reporting_To = (
Select
P_User_Reporting_To
From
P_USER_MSTR
Where
P_User_Emp_ID = <Emp ID>
And P_User_ID = <User ID>) As Temp
From
P_USER_MSTR PUM
,Temp

it returning : "Incorrect syntax near the keyword 'As'."

???

Mahesh

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-27 : 02:00:24

Your query above is syntax equal to this

UPDATE	P_USER_MSTR
SET	P_User_Active = 'N'
WHERE	P_User_Emp_ID = <Emp ID> 
	AND P_User_ID = <User ID>

But I doubt this is what you want to do. Try to explain the full thought process in your query and we will try to help with the query you really want.

Peter Larsson
Helsingborg, Sweden

mahesh_bote
Constraint Violating Yak Guru

298 Posts

Posted - 2006-11-27 : 02:28:42

quote:
Originally posted by Peso

Your query above is syntax equal to this
UPDATE P_USER_MSTR
SET P_User_Active = 'N'
WHERE P_User_Emp_ID = <Emp ID>
AND P_User_ID = <User ID>
But I doubt this is what you want to do. Try to explain the full thought process in your query and we will try to help with the query you really want.

Peter Larsson
Helsingborg, Sweden

What about the second field updation?, I m using Subquery over there? is it right or wrong? can't we use subquery, if no, where to use subquery, whether in FROM clause or WHERE clause?

Mahesh

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-27 : 02:32:46

Try to explain the full thought process in your query and we will try to help with the query you really want.

You are trying to update the subquery, which you can't do!
And what are <Emp ID> and <User ID> ??

Peter Larsson
Helsingborg, Sweden

mahesh_bote
Constraint Violating Yak Guru

298 Posts

Posted - 2006-11-27 : 02:41:18

this is my actual query

UPDATE
P_USER_MSTR
SET
P_User_Active = 'N'
,P_User_Reporting_To = Temp1.P_User_Reporting_To
FROM
(Select P_User_Reporting_To From P_USER_MSTR Where P_User_Emp_ID = <Emp ID> And P_User_ID = <User ID>) As Temp1
,(Select P_User_ID From P_USER_MSTR Where P_User_Emp_ID = <Emp ID> And P_User_ID = <User ID>) As Temp2
WHERE
Temp2.P_User_ID = <User ID>
And Temp1.P_User_Reporting_To = <Emp Id>

I m trying to use this update statment in SP, where <Emp Id>, <User Id> are parameters passed to SP. e.g. A, B, C are employees. Now if B quites, before B have to handover his task to his master and then he can, and at the same time C's manager will be A instead of B, if B handed over his task to A, if B hands over his task to D, C's manager will be D.

hopes i have explain all,

Mahesh

mahesh_bote
Constraint Violating Yak Guru

298 Posts

Posted - 2006-11-27 : 02:44:21

...

when i execute the above mentioned statement it returns, (0 row(s) affected)

why?

Mahesh

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-27 : 02:57:12

Is "P_User_Reporting_To" equal to UserID or EmpID?

Peter Larsson
Helsingborg, Sweden

mahesh_bote
Constraint Violating Yak Guru

298 Posts

Posted - 2006-11-27 : 03:05:50

table with sample data

User_ID User_Name User_Emp User_Reporting_To(Manager)
1 A 111 111
2 B 222 222
3 C 333 222
4 D 444 222

and so on

now suppose B retires. so he has to be inactive from User_Master, if we directly inactive such user, what about the users who reports to his manager(B, coz B is not active)? before that he has to hand over his task to his manager means B's manager must be 111 and at the same time User_Reporting_To of C, D must be updated to 111.

thanks,

Mahesh

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-27 : 03:14:50

[code]-- Set current user as inactive
UPDATE P_USER_MSTR
SET P_User_Active = 'N'
WHERE User_Emp = @EmpID
AND User_ID = @UserID

-- Update all employees who had current user as manager, to current user's manager
UPDATE x
SET x.User_Reporting_To = w.User_Reporting_To
FROM P_USER_MSTR AS x
INNER JOIN (
SELECT User_Emp,
User_Reporting_To
FROM MyTable
WHERE User_Emp = @EmpID
AND User_ID = @UserID
) w ON w.User_Emp = x.User_Reporting_To[/code]

Peter Larsson
Helsingborg, Sweden

mahesh_bote
Constraint Violating Yak Guru

298 Posts

Posted - 2006-11-27 : 04:07:01

still returning

(0 row(s) affected)

Mahesh

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-27 : 04:16:18

Your sample data is wrong

quote:
User_ID User_Name User_Emp User_Reporting_To(Manager)
1 A 111 111
2 B 222 222
3 C 333 222
4 D 444 222

User_ID User_Name User_Emp User_Reporting_To(Manager)
1 A 111 111
2 B 222 ~~222~~ 111
3 C 333 222
4 D 444 222

Peter Larsson
Helsingborg, Sweden

mahesh_bote
Constraint Violating Yak Guru

298 Posts

Posted - 2006-11-27 : 04:24:42

thanks Peso, for u'r efforts,

but i have tried to explain the process. I have made a little bit change to u'r query:

UPDATE x
SET P_User_Reporting_To = w.P_User_Reporting_To
,P_User_Active = 'N'
FROM P_USER_MSTR As x
INNER JOIN
(
SELECT P_User_Emp_ID,
P_User_Reporting_To
FROM P_USER_MSTR
WHERE P_User_Emp_ID = <Emp ID> AND P_User_ID = <User ID>
) w ON w.P_User_Reporting_To = x.P_User_Reporting_To
WHERE
x.P_User_Emp_ID = <Emp ID>
AND x.P_User_ID = <User ID>

thanks again,
Mahesh

mahesh_bote
Constraint Violating Yak Guru

298 Posts

Posted - 2006-11-27 : 04:27:50

but Peso,

Its updating only one field, Active. Also i m trying to update the User_Reporting_To field, which returns in subquery. can't we update more than one field in such situation or i have to write another update statement?

Mahesh

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-27 : 04:28:06

I give up...

Why are you trying to do that in ONE update statement?
If using your sample data with my correction, UserID 2 and EmpID 222 will do the right job in two updates.

Peter Larsson
Helsingborg, Sweden

mahesh_bote
Constraint Violating Yak Guru

298 Posts

Posted - 2006-11-27 : 06:35:32

UPDATE x
SET P_User_Reporting_To = w.P_User_Reporting_To
FROM P_USER_MSTR As x
,(SELECT
P_User_Reporting_To
FROM
P_USER_MSTR
WHERE
P_User_Emp_ID = <Emp ID>) As w
WHERE
x.P_User_Emp_ID IN
(SELECT
P_User_Emp_ID
FROM
P_USER_MSTR
WHERE
P_User_Reporting_To = <Emp ID>
And P_User_Active = 'Y')

problem solved,

thanks Peso,

I have implemented u'r suggestion,
(sorry for inconvenience)

Mahesh

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-11-27 : 07:38:48

quote:
Originally posted by mahesh_bote

or whats wrong with this query

Update
P_USER_MSTR
Set
P_User_Active = 'N'
,P_User_Reporting_To = (
Select
P_User_Reporting_To
From
P_USER_MSTR
Where
P_User_Emp_ID = <Emp ID>
And P_User_ID = <User ID>) As Temp
From
P_USER_MSTR PUM
,Temp

it returning : "Incorrect syntax near the keyword 'As'."

???

Mahesh

You cant use alias name in this subquery

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources