use search string stored in table

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

Transact-SQL (2000)

use search string stored in table

Author

Topic

qwertyjjj
Posting Yak Master

131 Posts

Posted - 2008-06-12 : 12:00:42

At present I have some hardcoded SQL to do this:


update invoices set credit_contname = 'John Smith' WHERE costcent = 'ZDTI' and cust_name like '[A-K]%' and cust_id not in (  
  select cust_id FROM Customers where accountcode IN (SELECT CustAccountCode FROM AllocationOverride) GROUP BY cust_id  
)

What I want to do is use a table dynamically.
So I have stored the search string in a table [A-K]
How do I use this in the search above?


update invoices set credit_contname = 'John Smith' WHERE costcent = 'ZDTI' and cust_name like (SELECT SearchString FROM Table) and cust_id not in (  
  select cust_id FROM Customers where accountcode IN (SELECT CustAccountCode FROM AllocationOverride) GROUP BY cust_id  
)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-06-12 : 12:37:44

Try this

update I set credit_contname = 'John Smith' from invoices I inner join SearchTable S
on I.cust_name like S.SearchString +'%'
WHERE costcent = 'ZDTI' and cust_id not in (
select cust_id FROM Customers where accountcode IN (SELECT CustAccountCode FROM AllocationOverride) GROUP BY cust_id)

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources