CODEWITHSUNDEEP
sqlsql-server
Niyas
Niyas

Reputation: 5

Update table using like operator

I have 2 tables table 1

Diagnosis            GroupID
155.0 - blaaaaaa      GAS

table 2

Code  GroupID  
155.0   
155.0
155.0
155.0

I did try to update GroupID of second table from first 

update Table2 set GroupID= GroupID from table 1 where Diagnosis like '%'+Code+'%'

but its not working

Upvotes: 0

Views: 161

Answers (3)

Robin Singh
Robin Singh

Reputation: 91

You can use the following code:

UPDATE 
Tb2 SET Tb2.GroupId = Tb1.GroupId
FROM table1 as Tb1 INNER JOIN table2 as Tb2
ON Tb1.Diagnosis LIKE '%'+Tb2.Code+'%';

Upvotes: 1

Vishwanath Dalvi
Vishwanath Dalvi

Reputation: 36651

This would help, http://rextester.com/NILBI39557

CREATE TABLE Table1
(
Diagnosis   VARCHAR(255)
,GROUPID VARCHAR(255)
);

Insert Into Table1 Values('155.0 - blaaaaaa','GAS');

CREATE TABLE Table2
(
Code      VARCHAR(255)
,GROUPID VARCHAR(255)
);

Insert Into Table2 VALUES ('155.0', NULL);
Insert Into Table2 VALUES ('155.0', NULL);
Insert Into Table2 VALUES ('155.0', NULL);
Insert Into Table2 VALUES ('155.0', NULL);

SELECT * FROM Table2;

UPDATE T2 
SET    T2.GroupId = T1.GroupId
FROM   Table1 as T1 INNER JOIN Table2 as T2
ON     T1.Diagnosis LIKE '%'+T2.Code+'%';

SELECT * FROM Table2;

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521914

Your current syntax for an update join is slightly off. Try using this instead:

UPDATE t2
SET GroupID = t1.GroupID
FROM Table2 t2
INNER JOIN Table1 t1
    ON t1.Diagnosis LIKE '%' + t2.Code + '%'

Upvotes: 1

Related Questions