Stucked on Update query - 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

Stucked on Update query

Author

Topic

chechu
Starting Member

9 Posts

Posted - 2011-04-12 : 04:16:08

Hi all,

I'm really stucked when trying to Update some colums value for all the returned rows. I need to find a way on how to perform that Update for each of the rows returned. Let me show you the Update I'm trying to execute:

Update Tbl_GenericBook
Set RPX_Nname = SUBSTRING(RPX_Name,4,LEN(RPX_Nname))
where RPX_Nname =(Select RPX_Nname
from Tbl_GenericBook
where RPX_Nname like 'Mr.%' and
RPX_Nname not like 'Mr.' and
RPX_Nname not like 'Mr. .' and
RPX_Nname not like 'Mr.. .' and
RPX_Nname not like 'Mr. ..' and
RPX_Nname not like 'Mr. .' and
RPX_Nname not like 'Mr. .' and
RPX_Nname not like 'Mr. ...')

Let me explain what I'm pretendig to do. RPX_Nname contains person names, but some customers added a "Mr." prefix. Not just this but they added several bullshit, like ',' , '..', etc... so I need to perform a select that only and only returns me rows with 'Mr.' + name, but no + any symbol.

First of all I performed a select ordered by RPX_Nname ASC to check all rare symbols that were added, and then I performed that filter with several statements.

Now I need to make this Update run, but I get following error:

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

Any1 knows how to perform this?

Thanks in advance!

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2011-04-12 : 04:27:28

Update Tbl_GenericBook
Set RPX_Nname = SUBSTRING(RPX_Name,4,LEN(RPX_Nname))
where RPX_Nname IN (Select RPX_Nname
from Tbl_GenericBook
where RPX_Nname like 'Mr.%' and
RPX_Nname not like 'Mr.' and
RPX_Nname not like 'Mr. .' and
RPX_Nname not like 'Mr.. .' and
RPX_Nname not like 'Mr. ..' and
RPX_Nname not like 'Mr. .' and
RPX_Nname not like 'Mr. .' and
RPX_Nname not like 'Mr. ...')

chechu
Starting Member

9 Posts

Posted - 2011-04-12 : 06:01:39

OMG... nevermind... didn't realized about that '='.

Thanks RikD.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources