SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Subquery In Update Statement
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mahesh_bote
Constraint Violating Yak Guru

India
298 Posts

Posted - 11/27/2006 :  01:34:04  Show Profile  Reply with Quote
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

India
298 Posts

Posted - 11/27/2006 :  01:49:59  Show Profile  Reply with Quote
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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30242 Posts

Posted - 11/27/2006 :  02:00:24  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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
Go to Top of Page

mahesh_bote
Constraint Violating Yak Guru

India
298 Posts

Posted - 11/27/2006 :  02:28:42  Show Profile  Reply with Quote
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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30242 Posts

Posted - 11/27/2006 :  02:32:46  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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

Edited by - SwePeso on 11/27/2006 02:33:47
Go to Top of Page

mahesh_bote
Constraint Violating Yak Guru

India
298 Posts

Posted - 11/27/2006 :  02:41:18  Show Profile  Reply with Quote
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
Go to Top of Page

mahesh_bote
Constraint Violating Yak Guru

India
298 Posts

Posted - 11/27/2006 :  02:44:21  Show Profile  Reply with Quote
...

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

why?

Mahesh
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30242 Posts

Posted - 11/27/2006 :  02:57:12  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Is "P_User_Reporting_To" equal to UserID or EmpID?


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

mahesh_bote
Constraint Violating Yak Guru

India
298 Posts

Posted - 11/27/2006 :  03:05:50  Show Profile  Reply with Quote
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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30242 Posts

Posted - 11/27/2006 :  03:14:50  Show Profile  Visit SwePeso's Homepage  Reply with Quote
-- 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


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

mahesh_bote
Constraint Violating Yak Guru

India
298 Posts

Posted - 11/27/2006 :  04:07:01  Show Profile  Reply with Quote
still returning

(0 row(s) affected)

Mahesh
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30242 Posts

Posted - 11/27/2006 :  04:16:18  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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

Edited by - SwePeso on 11/27/2006 04:16:38
Go to Top of Page

mahesh_bote
Constraint Violating Yak Guru

India
298 Posts

Posted - 11/27/2006 :  04:24:42  Show Profile  Reply with Quote
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
Go to Top of Page

mahesh_bote
Constraint Violating Yak Guru

India
298 Posts

Posted - 11/27/2006 :  04:27:50  Show Profile  Reply with Quote
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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30242 Posts

Posted - 11/27/2006 :  04:28:06  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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

Edited by - SwePeso on 11/27/2006 04:38:36
Go to Top of Page

mahesh_bote
Constraint Violating Yak Guru

India
298 Posts

Posted - 11/27/2006 :  06:35:32  Show Profile  Reply with Quote

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
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22755 Posts

Posted - 11/27/2006 :  07:38:48  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000