update and select from same table 1

[VBRookie]

I need a SQL statement that will select a value from a table and perform an update on that same table if the criteria is met. This is what I have but its not working:

update open_enrollment 
set ee_cost_year = (select ee_cost_year from open_enrollment where ssn = open_enrollment.ssn and benefit_id = "LIFE' and plan_id = 'OPTIONAL' and option_id = '1XSAL') 
where benefit_id = "LIFE' and plan_id = 'OPTIONAL' and option_id = '1XSAL'

can anyone help?

- VB Rookie

 

[rajeevnandanmishra]

Hi VB Rookie,
What error are you getting? Please, whenever you post, give your SQL server version no., and the error no./message. It will help others to identify the cause of problem.
By the way try to run your query like this :
update open_enrollment
set ee_cost_year = (select ee_cost_year from open_enrollment b where b.ssn = a.ssn and b.benefit_id = "LIFE' and b.plan_id = 'OPTIONAL' and b.option_id = '1XSAL')
from open_enrollment a
where a.benefit_id = "LIFE' and a.plan_id = 'OPTIONAL' and a.option_id = '1XSAL'

Let us know if you still face any problem.

 

[VBRookie]

Thanks rajeevnandanmishra,

A co-worker of mine was actually running into the problem so I don't know what exactly the error or error number was, but I have sent your revised SQL off to them and if it works I will surely let you know. Thank you greatly for your help.

- VB Rookie

 

[VBRookie]

rajeevnandanmishra,

IT WORKED! Here is some of what my elated co-worker had to say in an email:


THANKS! I tried the sql below and had a few problems...it doesn't seem to like the FROM row. I got it to work by taking out that row and replacing a.* with open_enrollment.*

Thanks again for your help.

- VB Rookie