June 19, 2012 at 6:08 pm
Hi everyone,
I'm trying to do a minor update in a table but have run into a small problem... Basically I have a table of 'Clients', and when a client leaves a termination date is entered and a 'ZZ' is appended to the start of the ClientName field. Quite a few clients have a termination date but their name is yet to have 'ZZ' appended to their name, so I was looking at doing the following:
Update Clients
Set ClientName = 'ZZ ' + convert(nvarchar(max),ClientName)
Where ClientTerminationDate Is Not NULL And ClientName Not Like 'ZZ%'
That all seemed good but I get the following error:
Cannot insert duplicate key row in object 'Clients' with unique index 'ClientName'.
I can sort of understand what it's saying; you can't change a field where that same field is being used to identify the field to be changed. But how else would I do this? I only want to 'ZZ' those names that have a termination date and haven't already been ZZ'ed. I guess I'd need to identuify these clients then store a reference to them to do the actual change?
Any ideas would be a great help! Thanks.
June 19, 2012 at 7:07 pm
Something like this should get you there:
Update c1
Set ClientName = 'ZZ ' + convert(nvarchar(max),ClientName)
FROM Clients c1
LEFT JOIN Clients c2 ON 'ZZ ' + convert(nvarchar(max),c1.ClientName) = c2.ClientName
Where ClientTerminationDate Is Not NULL And ClientName Not Like 'ZZ%' AND c2.ClientName IS NULL
This assumes that ClientName is the sole primary key. If there are more fields in the primary key, those should be included after ON.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
June 19, 2012 at 7:08 pm
Post deleted because I mistakenly quoted above instead of editing it. 😀
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
June 19, 2012 at 7:38 pm
I'm thinking that the problem is that some clients have already been marked with "ZZ" as a prefix and that if you were to add prefixes to all the clients, you'd find duplicates. Try copying all of the client names to a test table with no constraints, update them all to have a "ZZ" prefix, and then do a dupe check. I believe you'll see what I mean.
This is also why keys should always be immutable.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 19, 2012 at 8:37 pm
Yes thats correct some of the clients have been terminated correctly with the 'ZZ ' appended, so I want to azoid 'ZZ ZZ 'ing them.
And the 'ClientName' field isn't the primary key, there's a sole 'ClientID' field in the 'Clients' table as well, does that make a difference to being able to solve it in one step? I was thinking I'd need to copy them into a table to do the update like you mention.
Thanks for the help!
June 19, 2012 at 8:44 pm
Jacobs_r86 (6/19/2012)
Yes thats correct some of the clients have been terminated correctly with the 'ZZ ' appended, so I want to azoid 'ZZ ZZ 'ing them.And the 'ClientName' field isn't the primary key, there's a sole 'ClientID' field in the 'Clients' table as well, does that make a difference to being able to solve it in one step? I was thinking I'd need to copy them into a table to do the update like you mention.
Thanks for the help!
You should still be able to do the LEFT JOIN as I suggested.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
June 19, 2012 at 11:08 pm
Thanks dwain, that looked to work... somewhat. I just ran some checks and it missed one, or rather it seemed to 'ZZ' its entry in the c2 referenced table and not the c1? I've seen that reference approach used before but not entirely up to speed on it. c1 and c2 are just pointing at the same 'Clients' table aren't they? So how can c1 ClientName not have the 'ZZ' but the c2 ClientName does?
June 19, 2012 at 11:25 pm
Jacobs_r86 (6/19/2012)
Thanks dwain, that looked to work... somewhat. I just ran some checks and it missed one, or rather it seemed to 'ZZ' its entry in the c2 referenced table and not the c1? I've seen that reference approach used before but not entirely up to speed on it. c1 and c2 are just pointing at the same 'Clients' table aren't they? So how can c1 ClientName not have the 'ZZ' but the c2 ClientName does?
What you are calling a reference is actually referred to as the table alias. Let's look at the code again:
Update c1
Set ClientName = 'ZZ ' + convert(nvarchar(max),ClientName)
FROM Clients c1
LEFT JOIN Clients c2 ON 'ZZ ' + convert(nvarchar(max),c1.ClientName) = c2.ClientName
Where ClientTerminationDate Is Not NULL And ClientName Not Like 'ZZ%' AND
c2.ClientName IS NULL
I added two parts to it. The LEFT JOIN and "AND c2.ClientName is NULL"
1. LEFT JOIN joins the table onto itself as you have deduced (c1 and c2 refer to the same table). The c2 table should already have ZZ applied to its ClientNames. So doing the LEFT JOIN as I have should result in c2.ClientName being non-NULL in cases where that ZZ-ClientName already exists.
2. The addition to the WHERE just makes sure that you only allow the UPDATE where the ClientName (with the ZZ) does not exist.
I'm not sure why it might have missed one without having more information.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
June 20, 2012 at 12:11 am
I see what you mean and it makes sense but there were 39 records that got updated out of the possible 40. It looks to be failing on 'c2.ClientName IS NULL', as that field has the ZZ'ed SortName in it. But if I were to take that clause out, then i'll have the problem you mention in point 2.
One thing, I get 'ambiguous' errors with your code but I understand you mean the following:
UPDATE c1
SET ClientName = 'ZZ ' + convert(nvarchar(max),ClientName)
FROM Clients c1
LEFT JOIN Clients c2 ON 'ZZ ' + convert(nvarchar(max),c1.ClientName) = c2.ClientName
WHERE c1.ClientTerminationDate IS NOT NULL AND c1.ClientName NOT LIKE 'ZZ%' AND
c2.ClientName IS NULL
Also, Just out of interest I removed the ZZ on one of the clients that had it automatically applied and when i run a lookup (minus the "WHERE c2.ClientName IS NULL") I get both records with the one I just manually removed the ZZ from having NULL in c2.ClientName field and the one that didn't update having its ZZed ClientName in there. So I'm not sure whats going on...
June 20, 2012 at 12:19 am
Jacobs_r86 (6/20/2012)
I see what you mean and it makes sense but there were 39 records that got updated out of the possible 40. It looks to be failing on 'c2.ClientName IS NULL', as that field has the ZZ'ed SortName in it. But if I were to take that clause out, then i'll have the problem you mention in point 2.One thing, I get 'ambiguous' errors with your code but I understand you mean the following:
UPDATE c1SET ClientName = 'ZZ ' + convert(nvarchar(max),ClientName)
FROM Clients c1
LEFT JOIN Clients c2 ON 'ZZ ' + convert(nvarchar(max),c1.ClientName) = c2.ClientName
WHERE c1.ClientTerminationDate IS NOT NULL AND c1.ClientName NOT LIKE 'ZZ%' AND
c2.ClientName IS NULL
Also, Just out of interest I removed the ZZ on one of the clients that had it automatically applied and when i run a lookup (minus the "WHERE c2.ClientName IS NULL") I get both records with the one I just manually removed the ZZ from having NULL in c2.ClientName field and the one that didn't update having its ZZed ClientName in there. So I'm not sure whats going on...
You might want to try this to help diagnose your problem.
SELECT c1.*, c2.*
FROM Clients c1
LEFT JOIN Clients c2 ON 'ZZ ' + convert(nvarchar(max),c1.ClientName) = c2.ClientName
WHERE c1.ClientTerminationDate IS NOT NULL AND c1.ClientName NOT LIKE 'ZZ%' AND
c2.ClientName IS NULL
The ambiguous error was probably on this:
convert(nvarchar(max),ClientName)
Which should have been this:
convert(nvarchar(max),c1.ClientName)
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
June 20, 2012 at 12:48 am
Thanks dwain, found the problem (which I would have found a lot quicker if I took note of your first reply in relation to primary keys). There were 2 records with the same ClientName, or rather a client left and was ZZed then returned and rather then reactivating their old terminated account (removing the ZZ) someone created a new account in the same name. Then it looks like the client has left again hence another termination date was entered on the newer account but attempts to have the ClientName ZZed again throw an error because that name already exists.
The defining factor here though is that they had different ClientIDs. Thanks for all your help with this, I've learnt a lot!
June 20, 2012 at 12:54 am
Jacobs_r86 (6/20/2012)
Thanks dwain, found the problem (which I would have found a lot quicker if I took note of your first reply in relation to primary keys). There were 2 records with the same ClientName, or rather a client left and was ZZed then returned and rather then reactivating their old terminated account (removing the ZZ) someone created a new account in the same name. Then it looks like the client has left again hence another termination date was entered on the newer account but attempts to have the ClientName ZZed again throw an error because that name already exists.The defining factor here though is that they had different ClientIDs. Thanks for all your help with this, I've learnt a lot!
Glad to hear I have another satisfied client!
No need to terminate your account at this time. 😛 ZZzzz...
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
June 20, 2012 at 1:05 am
😀 This forum has proven a handy resource so I'll be sticking around, especially now I have a post count above 1 😎
June 29, 2012 at 11:23 pm
Should have C2 Table ZZ used in its ClientNames.
Try harder and harder to know Platform thick heels shoes everywhere!
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply