Different Join problem. - 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 2000 Forums

SQL Server Development (2000)

Different Join problem.

Author

Topic

BuddyRam
Starting Member

17 Posts

Posted - 2007-06-28 : 03:27:04

Hi all,
I would like to update table1 column id with values of table2.ID based on coulm Name.
The condition will be I need toi update the all rows in table1.ID with correspoding Table2.ID based on if the table2.name column is same or substring of table1.name
ex:
if table1.name column is having "hello friend" and if thable2.name Coulmn is having "hello" then i need to update table1.ID = table2.ID.

for that hw can I write a simple update SQL statement?
Thanks

Cheers
Ram

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-06-28 : 03:43:35

[code]Update t1
set ID = t2.ID
From Table1 t1 JOIN Table2 t2 on t1.name like '%' + t2.name + '%'[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Koji Matsumura
Posting Yak Master

141 Posts

Posted - 2007-06-28 : 03:49:11

table1.ID with corresponding Table2.ID means they have same values?
Why Update?

BuddyRam
Starting Member

17 Posts

Posted - 2007-06-28 : 05:10:10

no, I have Table1.ID is having new ID values but it got changed to new ID values in Table2, so I have to update with same ID in table1 if table1.name and tabl2.name columns having similar values.
I will explain in details.
In table1 the name field is having entries like
1 The Times
2 The Times [Busssiness']
but in table2 is having like
101 TheTimes

So now I have to update Table1.ID with 101 for two above mentioned coulmns since these two rows are for The Times entry.

Cheers
Ram

Koji Matsumura
Posting Yak Master

141 Posts

Posted - 2007-06-28 : 05:36:07

In above case, what if table2 has 102 TheTIME?
Should the ID of Table1 is 101 or 102?
If you don't have such case, Harsh's solution should work.

BuddyRam
Starting Member

17 Posts

Posted - 2007-06-29 : 05:59:45

But I am bit wondering how this wuery is updating??can you explain please?
Thanks

Cheers
Ram

Koji Matsumura
Posting Yak Master

141 Posts

Posted - 2007-06-29 : 06:43:30

which query?