SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Updating with Self join
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Grifter
Constraint Violating Yak Guru

263 Posts

Posted - 05/15/2013 :  04:23:24  Show Profile  Reply with Quote
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)


And I want to update to:


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

India
52325 Posts

Posted - 05/15/2013 :  04:28:42  Show Profile  Reply with Quote
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

263 Posts

Posted - 05/15/2013 :  11:41:59  Show Profile  Reply with Quote
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

India
52325 Posts

Posted - 05/16/2013 :  00:12:20  Show Profile  Reply with Quote
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

263 Posts

Posted - 05/16/2013 :  06:06:00  Show Profile  Reply with Quote
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.


Edited by - Grifter on 05/16/2013 06:07:47
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 05/16/2013 :  06:14:03  Show Profile  Reply with Quote
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

263 Posts

Posted - 05/16/2013 :  07:35:51  Show Profile  Reply with Quote
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

India
52325 Posts

Posted - 05/16/2013 :  07:49:00  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000