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 James123456 James, (123456)[code] And I want to update to:[code]Work_no Work_name========= =========123456 James123456 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) > 0Only 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?ThanksGrifter |
|
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 t1JOIN table t2ON t2.[Work name] LIKE t1.[Work name] + ',%' once happy remove select line and uncomment update and execute------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
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 t1JOIN table t2ON t2.[Work name] LIKE t1.[Work name] + ',%' once happy remove select line and uncomment update and execute------------------------------------------------------------------------------------------------------SQL Server MVPhttp://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. |
|
|
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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
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 MVPhttp://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. |
|
|
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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
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 MVPhttp://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 bset b.work_name = a.work_namefrom table b inner join table a ON b.work_no = a.work_nowhere charindex(',',b.work_name) > 0 |
|
|
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 formulateAnyways 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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|