Update Qusetion on 'LIKE' - 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)

Update Qusetion on 'LIKE'

Author

Topic

Bill_C
Constraint Violating Yak Guru

299 Posts

Posted - 2014-10-16 : 02:23:55

Hi

I have a temp table '#t1' that I am creating from a couple of other tables which then gives me 5 fields, lets say f1,f2,f3,f4 which are all text fields and f5 which is numeric.

Now I want to update the field f5 from 0 to 1 where the contents of field f4 are LIKE the value in field f2.

I've tried

UPDATE #t1 SET f5 = 1 WHERE f4 LIKE (SELECT '''%'+f2+'%''' FROM #t1)

But I'm getting errors 'Subquery returned more than 1 value.'

Any ideas where i'm going wrong please?

stepson
Aged Yak Warrior

545 Posts

Posted - 2014-10-16 : 02:42:28

I will try something like this:


UPDATE #t1 
SET f5 = CASE WHEN F4 LIKE '%'+F2+'%' THEN 1 ELSE 0 END

my test:


IF OBJECT_ID('tempDB..#t1') IS NOT NULL
BEGIN
	DROP TABLE tempDB..#t1
END

CREATE TABLE #t1
(
	f1 INT,
	f2 VARCHAR(100),
	f3 VARCHAR(100),
	f4 VARCHAR(100),
	f5 TINYINT
)

INSERT INTO #t1 (f1,f2,f3,f4)
SELECT 1,'test','field3','xxstestsss'
UNION ALL
SELECT 2,'tezt','field3','xxxtez'
UNION ALL
SELECT 3,'something','field3','field4'

UPDATE #t1 
SET f5 = CASE WHEN F4 LIKE '%'+F2+'%' THEN 1 ELSE 0 END




SELECT * FROM #t1
DROP TABLE tempDB..#t1

sabinWeb MCP

Bill_C
Constraint Violating Yak Guru

299 Posts

Posted - 2014-10-16 : 02:50:42

Thanks for that, a little more elegant than what I've just tried (which seemed to work!)

UPDATE #t1 SET f5 = '1' WHERE f4 IN
(SELECT c.f4 FROM #t2 c INNER JOIN #t2 b
ON c.f4 = b.f4 WHERE c.f4 LIKE '%'+ b.f2+'%')

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources