SQL Update from Join with Field

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)

SQL Update from Join with Field

Author

Topic

bholmstrom
Yak Posting Veteran

76 Posts

Posted - 2013-10-23 : 13:12:53

Good afternoon,

I have 2 tables (table1, table2) They both contain a filed named office which is what I am using to "join" the 2 tables.

After the join then I would like to update a field (table1.omp) base on the condition that the Table1.DisplayName is in the Table2.OMP field. The table2.omp field can contain multiple names seperated by an ";"

Here is where is am right now. I only get one persons name back. There should be around 30 or so.

-------- Query ----------
SELECT TB1.DISPLAYNAME ,
TB1.Office,
TB2.Office,
TB2.OMP
FROM
Table1 TB1
LEFT JOIN
Table2 TB2
ON
Table1.Office
= Table2.Office

WHERE TB1.DISPLAYNAME like '%' + TB2.OMP + '%'

Bryan Holmstrom

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-23 : 13:46:38

[code]
SELECT TB1.DISPLAYNAME ,
TB1.Office,
TB2.Office,
TB2.OMP
FROM
Table1 TB1
LEFT JOIN
Table2 TB2
ON
Table1.Office
= Table2.Office

WHERE ';' + TB2.OMP + ';' LIKE '%;' + TB1.DISPLAYNAME + ';%'
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

bholmstrom
Yak Posting Veteran

76 Posts

Posted - 2013-10-23 : 14:43:44

Thank you for the prompt answer. The query does not work when the field contains ";" though.

An Example: Ivana Vasic-Lalovic; Carolina Cabral

Bryan Holmstrom

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-10-23 : 14:59:42

There are couple of things:

1. The issue may be that you have a space after the semi-colon. Test if that is the problem by changing the where clause to

WHERE  ';' + REPLACE(TB2.OMP,'; ',';') + ';' LIKE '%;' + TB1.DISPLAYNAME + ';%'

That is only for testing. It would still fail if you had multiple spaces after the semi-colon.

2. You are using a left join along with a where clause that contains a field from the right table. That effectively turns it into an inner join. That is not the issue in this particular example, but I don't know enough about your data to say whether that will be an issue in other scenarios.

bholmstrom
Yak Posting Veteran

76 Posts

Posted - 2013-10-23 : 15:11:15

That was it, fantastic. The fields are all seperated by a "; "

Again Thank you for your fast response.

Bryan Holmstrom

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-10-23 : 16:43:13

Great - glad it helped.

But I would be concerned about the data having more than one space, especially so if the data is generated via human input. If that is a consideration, another approach is to split the data into a virtual table using semi-colon as the separator, trim leading and trailing spaces, and use that virtual table to join to TB1.DISPLAYNAME.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources