| Author |
 Topic  |
|
|
mahesh_bote
Constraint Violating Yak Guru
India
298 Posts |
 Posted - 11/27/2006 : 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
India
298 Posts |
Posted - 11/27/2006 : 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
Sweden
29138 Posts |
Posted - 11/27/2006 : 02:00:24
|
Your query above is syntax equal to thisUPDATE 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
India
298 Posts |
Posted - 11/27/2006 : 02:28:42
|
quote:
Originally posted by Peso
Your query above is syntax equal to thisUPDATE 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
Sweden
29138 Posts |
Posted - 11/27/2006 : 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 |
Edited by - SwePeso on 11/27/2006 02:33:47 |
|
|
|
mahesh_bote
Constraint Violating Yak Guru
India
298 Posts |
Posted - 11/27/2006 : 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
India
298 Posts |
Posted - 11/27/2006 : 02:44:21
|
...
when i execute the above mentioned statement it returns, (0 row(s) affected)
why?
Mahesh |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 11/27/2006 : 02:57:12
|
Is "P_User_Reporting_To" equal to UserID or EmpID?
Peter Larsson
Helsingborg, Sweden |
|
|
|
mahesh_bote
Constraint Violating Yak Guru
India
298 Posts |
Posted - 11/27/2006 : 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
Sweden
29138 Posts |
Posted - 11/27/2006 : 03:14:50
|
-- 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 |
|
|
|
mahesh_bote
Constraint Violating Yak Guru
India
298 Posts |
Posted - 11/27/2006 : 04:07:01
|
still returning
(0 row(s) affected)
Mahesh |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 11/27/2006 : 04:16:18
|
Your sample data is wrongquote:
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 |
|
|
|
mahesh_bote
Constraint Violating Yak Guru
India
298 Posts |
Posted - 11/27/2006 : 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
India
298 Posts |
Posted - 11/27/2006 : 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
Sweden
29138 Posts |
Posted - 11/27/2006 : 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 |
Edited by - SwePeso on 11/27/2006 04:38:36 |
|
|
|
mahesh_bote
Constraint Violating Yak Guru
India
298 Posts |
Posted - 11/27/2006 : 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
India
22461 Posts |
Posted - 11/27/2006 : 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 |
|
|
| |
Topic |
|
Subquery In Update Statement
