Problem in Update with SubQuery

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 2005 Forums

Transact-SQL (2005)

Problem in Update with SubQuery

Author

Topic

kMargaret
Starting Member

3 Posts

Posted - 2010-06-07 : 06:33:30

Hi,

I have a problem with the following SQL:

update emp set profile = '5' where emp_id in(select emp_id from emp,sal where emp.is_manager= '1'
and sal.salary > '25000' and emp.emp_id = sal.emp_id)

Getting below error when i execute the query.

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Not able to understand whats the mistake here as i am using "in" as we get more than one record from the subquery.

anybody know the reason?please do help.

Thanks in advance

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2010-06-07 : 06:47:45

try like this
update e
set profile = '5'
FROM emp e
inner join sal s on s.emp_id = e.emp_id
where e.is_manager= '1' and s.salary > '25000'

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-06-07 : 06:56:43

Just a shot in dark

Try this:

update emp set profile = '5' where emp_id in (select e.emp_id from emp e,sal s where e.is_manager= '1'
and s.salary > '25000' and e.emp_id = s.emp_id)

Update Emp set Profile ='5'
From Emp e join Sal s on e.emp_id = s.emp_id
and e.is_manager= '1'
and s.salary > '25000'

Regards,
Bohra

I am here to learn from Masters and help new bees in learning.

kMargaret
Starting Member

3 Posts

Posted - 2010-06-07 : 07:11:13

Thanks for ur reply. But its not working.still same error is coming.
Any other ways please!

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-06-07 : 07:25:42

Are you sure that you are showing us the real executed query?

No, you're never too old to Yak'n'Roll if you're too young to die.

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2010-06-07 : 07:46:02