QUERY WONT UPDATE - 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 2008 Forums

Transact-SQL (2008)

QUERY WONT UPDATE

Author

Topic

jayram11
Yak Posting Veteran

97 Posts

Posted - 2010-08-06 : 10:41:34

I have the following query. the select returns 188 records and when i run the update it says 188 rows affected but it does not change the values that it is set to, any reason y,
thanks

select * from data..oce
where eff_beg = '04/01/2010' and
imp_beg = '10/01/2007' and
hcpcs in (select HCPCS_k from HELPER..OCE2010 where dev_has_proc_s <> dev_has_proc_t)
---188

update data..oce
set dev_has_proc = a.dev_has_proc_s from HELPER..OCE2010 as a
where eff_beg = '04/01/2010' and imp_beg = '10/01/2007' and hcpcs in (select HCPCS_k from HELPER..OCE2010 where dev_has_proc_s <> dev_has_proc_t)

oldjeep
Starting Member

5 Posts

Posted - 2010-08-06 : 11:18:16

You don't appear to be joining HELPER..OCE2010 to data..oce anywhere, which would result in all of your updates being updated with the first? value returned from HELPER..OCE2010

Chuck P

X002548
Not Just a Number

15586 Posts

Posted - 2010-08-06 : 13:35:34

you need to correlate the rows somehow


UPDATE o
   SET dev_has_proc = a.dev_has_proc_s 
  FROM HELPER..OCE2010 as a
  JOIN data..oce o
    ON a.??? = o.???
 WHERE eff_beg = '04/01/2010' 
   AND imp_beg = '10/01/2007' 
   AND hcpcs IN (SELECT HCPCS_k 
				   FROM HELPER..OCE2010 
				  WHERE dev_has_proc_s <> dev_has_proc_t)

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

jayram11
Yak Posting Veteran

97 Posts

Posted - 2010-08-09 : 09:17:34

Thank you

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources