CODEWITHSUNDEEP
sqlreplacematchingsql-like
codingknob
codingknob

Reputation: 11670

sql: update table1 based on lookup match in another table2

I have 2 tables:

table1:

NULL    NULL    Cat.XX 23   Cow.XX 87
NULL    NULL    Tiger.XX 99 Elephant.XX

Column1 and Column2 are ID numbers that are associated with the value in column3 and column4 respectively.

table2:

84048713    Cat.XX 23   Blah1   Blah2   Blah3   Blah4   
44008714    Elephant.XX 77  Blah1   Blah2   Blah3   Blah4   
64038715    Cow.XX 87   Blah1   Blah2   Blah3   Blah4
34058716    Tiger.XX 99 Blah1   Blah2   Blah3   Blah4
74038717    Zebra.XX 34 Blah1   Blah2   Blah3   Blah4
94098719    Whale.XX 47 Blah1   Blah2   Blah3   Blah4

I want to update each row in table1 with the appropriate ID numbers. The resulting table1 should look like the following:

84048713    64038715    Cat.XX 23   Cow.XX 87
34058716    44008714    Tiger.XX 99 Elephant.XX

I have tried various combinations of using select, where and select replace (I use replace because the fields that contain the animal names have spaces in them). For example I tried the following:

select IDs from table2 where 
(select replace("Name", ' ', '') from table2
LIKE
(select replace("Name", ' ', '') from table1)

But I get the following error:

Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

I appreciate your help. Thank you.

Upvotes: 0

Views: 810

Answers (3)

Hiren Dhaduk
Hiren Dhaduk

Reputation: 2780

enter image description here

UPDATE TABLE_1 SET ID = B.ID , ID2 = C.ID FROM TABLE_1 AS A LEFT OUTER JOIN TABLE_2 AS B ON A.TEMPID = B.TEMPID LEFT OUTER JOIN TABLE_2 AS C ON A.TEMPUD2 = C.TEMPID

Upvotes: 0

JohnZ
JohnZ

Reputation: 482

update table1 set Column1ID = (select ID from table2 where column2 = table1.column3),Column2ID = (select ID from table2 where column2 = table1.column4)

Upvotes: 1

Kaf
Kaf

Reputation: 33839

Try this;

Update t1
Set t1.col1 = case t1.col3 when t2.col2 then t2.col1 else t1.col1, 
    t1.col2 = case t1.col4 when t2.col2 then t2.col1 else t1.col2
From table1 t1 join table2 t2 
    on t1.col3 = t2.col2 or t1.col4 = t2.col2

Upvotes: 1

Related Questions