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
 General SQL Server Forums
 New to SQL Server Programming
 Updating with Self join

Author  Topic 

Grifter
Constraint Violating Yak Guru

274 Posts

Posted - 2013-05-15 : 04:23:24
Due to combining old and new system data in a table I have a list of data like this:


Work no Work name
========= =========
123456 James
123456 James, (123456)
[code]

And I want to update to:

[code]
Work_no Work_name
========= =========
123456 James
123456 James


I tried building an update statement, wasn't too confident in it so ran it as an equivalent select statement to see what returned and it seems to be running in an infinite loop (there's about 200k records and when I stopped it it was at somewhere in 2 Million returned!) although what it was returning at the start looked fine it just seemed to be duplicating or something:

UPDATE c1
set c1.Work_name = c.Work_name
FROM table c1
INNER JOIN table c ON c1.Work_no = c.Work_no
where charindex(',',c1.Work_name) > 0

Only got experience doing the simplest update statements - a bit stuck with this one if anyone could suggest what I am doing wrong and best way to rectify it?

Thanks

Grifter

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-15 : 04:28:42
try the below select first

--UPDATE t1
--SET t1.[Work name]=t2.[Work name]
SELECT t1.[Work name],t2.[Work name]
FROM table t1
JOIN table t2
ON t2.[Work name] LIKE t1.[Work name] + ',%'

once happy remove select line and uncomment update and execute

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Grifter
Constraint Violating Yak Guru

274 Posts

Posted - 2013-05-15 : 11:41:59
quote:
Originally posted by visakh16

try the below select first

--UPDATE t1
--SET t1.[Work name]=t2.[Work name]
SELECT t1.[Work name],t2.[Work name]
FROM table t1
JOIN table t2
ON t2.[Work name] LIKE t1.[Work name] + ',%'

once happy remove select line and uncomment update and execute

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




Apologies I never fully explained the difference in the names so maybe a wee bit more complex a solution. They are like this below:


Work_no Work_name
========= =========
123456 James, B (123456)
123456 Brian James


I was going to try some code with a string taking the last names and comparing these but there may be different people with same surname.

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-16 : 00:12:20
then how will you detrmine which name record needs to be updated to which? Is it based on Work_no?

In above case what should be final name value you need to see in the records?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Grifter
Constraint Violating Yak Guru

274 Posts

Posted - 2013-05-16 : 06:06:00
quote:
Originally posted by visakh16

then how will you detrmine which name record needs to be updated to which? Is it based on Work_no?

In above case what should be final name value you need to see in the records?

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




The name James, B (123456) should be updated to Brian James. I didn't see how I could update it using the work number as the old and new name has the same number so would need some sort of string identifier to recognise the name and then update it to the name that is matching then umber but doesn't have the distinct pattern.

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-16 : 06:14:03
but still my question stands. Which value needs to be updated with which? whats the rule on that?

FOr example in above case you could also update record with Brian James to James, B (123456) so you need to specify a rule first may be like longest string to be taken and updated on all other values for each Work_no group etc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Grifter
Constraint Violating Yak Guru

274 Posts

Posted - 2013-05-16 : 07:35:51
quote:
Originally posted by visakh16

but still my question stands. Which value needs to be updated with which? whats the rule on that?

FOr example in above case you could also update record with Brian James to James, B (123456) so you need to specify a rule first may be like longest string to be taken and updated on all other values for each Work_no group etc

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




I already answered that saying James, B (123456) > Brian James - each has the same key so I used charindex to identify them. The following worked for me:


update b
set b.work_name = a.work_name
from table b inner join table a
ON b.work_no = a.work_no
where charindex(',',b.work_name) > 0


Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-16 : 07:49:00
Nope..Thats for this one instance. What if you dont have a value with , at all for a work_no group? Thats why I suggested there should be a generic rule you've to formulate
Anyways if it works for you for all the values currently, then its fine and I hope you wont have any exceptions in future too.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
   

- Advertisement -