Update field based on Count - 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

General SQL Server Forums

New to SQL Server Programming

Update field based on Count

Author

Topic

Vack
Aged Yak Warrior

530 Posts

Posted - 2011-03-28 : 12:03:09

I want to update a field with a value based on the count of a certain field.


Table: bmprdstr

Item_no    comp_item_no    loc
PARENTA       COMP1
PARENTA       COMP2M
PARENTB       COMP4
PARENTB       COMP5

What I want to do is if an Item_no contains a component item that ends with "M" I want to update the loc to "MWP"

Here is my attempt, but I know I'm way off here. Any help would be appreciated.


update bmprdstr_sql
set loc = 'MWP'
where (select count(right(rtrim(comp_item_no),1)) 
from bmprdstr_sql
where right(rtrim(comp_item_no),1)='M')

If get the following msg with the above code
An expression of non-boolean type specified in a context where a condition is expected, near ')'.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-03-28 : 12:23:50

You shouldn't need the select.

update bmprdstr
set loc = 'MWP'
where comp_item_no like '%M'

Vack
Aged Yak Warrior

530 Posts

Posted - 2011-03-28 : 12:28:58

That will only update the 2nd record in my data example above. I want both the first and 2nd record updated to MWP.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-03-28 : 12:38:51

Sorry, I misread/misunderstood. I hope second time is the charm. If not it WILL be the third time, I promise :--)

update b1
set loc = 'MWP'
from
   bmprdstr2 b1
   inner join bmprdstr2 b2 on b2.Item_no = b1.Item_No
where b2.comp_item_no like '%M'

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-03-28 : 12:47:03

Or this, same as sunitabeck's, but may be a bit faster

UPDATE b1
SET loc = 'MWP'
FROM bmprdstr b1
WHERE EXISTS (select *
from bmprdstr b2
where b1.item_no = b2.item_no
and b2.comp_item_no like '%M'
)

Jim

Everyday I learn something that somebody else already knew

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources