SQL Update
-
I need some help with an SQL query I’ve been asked to create. Due to lack of communication, an update was done incorrectly and now needs to be fixed (surprise, surprise :-D ). Each employee has 5 Ben_Codes and the start date for all Ben_Codes needs to be equal to the start date of Ben_Code 1. The dates are not constant so for each employee I need to read Ben_Code 1 record to retrieve start_date and then update Ben_Codes 2 thru 5 with that start_date. To this point I have done very basic updates using SQL Query and would appreciate any guidance you can provide!! Emp_id, Ben_Code, start_date 99999 1 10/12/12 99999 2 01/12/13 99999 3 01/12/13 99999 4 01/12/13 99999 5 01/12/13 98989 1 9/15/2012 98989 2 01/12/13 98989 3 01/12/13 98989 4 01/12/13 98989 5 01/12/13
Here's how I'd do it.
UPDATE BEN
SET Start_date=B.Start_date
FROM BEN A
INNER JOIN
(
SELECT EmpId
, Start_date
FROM BEN
WHERE Ben_code=1
) B
ON A.EmpId=B.EmpID
WHERE A.Ben_code<>1 -- optionalAlso get in the habit of using transactions when doing such things.
-
Just so I'm clear on what you guys are saying..... It's not a huge concerned that it will rescan the table/index because this is a one production time fix and won't be running in production on a daily/weekly basis.....right?
mrfalk wrote:
one production time fix and won't be running in production
That was my point, I was just being bitchy at Piebald, something I do regularly.
Never underestimate the power of human stupidity RAH
-
Exactly. So is this how the responder writes production code when it matters? Is this as much as the OP will learn? And he'll now write this way? Perfect practice makes perfect. Do it the right way every time. I see too much bad SQL being written. It should be nipped in its bud.
PIEBALDconsult wrote:
So is this how the responder writes production code when it matters
Rubbish, fix the problem, fix it now and save the script, if it needs repeating then spend the time to make it elegant and efficient. Knowing that it is a kludge is more important than making a one off elegant.
Never underestimate the power of human stupidity RAH
-
Exactly. So is this how the responder writes production code when it matters? Is this as much as the OP will learn? And he'll now write this way? Perfect practice makes perfect. Do it the right way every time. I see too much bad SQL being written. It should be nipped in its bud.
PIEBALDconsult wrote:
Perfect practice makes perfect. Do it the right way every time.
Nope. Not in the real world. The real world is about money. Getting it perfect every time takes time and thus costs money. In the real world it isn't possible to get it perfect and most of the time it will not have any significant impact on actual required production functionality. Thus the extra cost is lost revenue which will never be regained. This of course isn't the same as saying that one can write poor code all the time. But rather one must learn to recognize that ones time is in fact valuable and thus one must prioritize what one spends time on. And since, again in the real world, one can not spend an infinite amount of time on everything, one must make compromises. And one might specifically look at this thread as an example and note that without knowing about the actual business of which this artifact is a part one will not have any idea about specifics such as size of the table, usage statistics, allowed maintenance, etc. And when one looks at all possible tables in all possible businesses, on average it won't matter.
PIEBALDconsult wrote:
I see too much bad SQL being written. It should be nipped in its bud.
Myself I see a lot of average code being written - it isn't specific to any language. Which is quite comforting since otherwise humans wouldn't be the ones writing it. If you are seeing a lot of poor SQL then maybe you need to start looking at a broader range of sources.
-
I need some help with an SQL query I’ve been asked to create. Due to lack of communication, an update was done incorrectly and now needs to be fixed (surprise, surprise :-D ). Each employee has 5 Ben_Codes and the start date for all Ben_Codes needs to be equal to the start date of Ben_Code 1. The dates are not constant so for each employee I need to read Ben_Code 1 record to retrieve start_date and then update Ben_Codes 2 thru 5 with that start_date. To this point I have done very basic updates using SQL Query and would appreciate any guidance you can provide!! Emp_id, Ben_Code, start_date 99999 1 10/12/12 99999 2 01/12/13 99999 3 01/12/13 99999 4 01/12/13 99999 5 01/12/13 98989 1 9/15/2012 98989 2 01/12/13 98989 3 01/12/13 98989 4 01/12/13 98989 5 01/12/13
If each employee has five Ben_Codes that each have a date that needs to be the same but can be different, then you should consider normalizing the database a bit.
People say nothing is impossible, but I do nothing every day.
-
Won't that rescan the table/index for each id?
PIEBALDconsult wrote:
Won't that rescan the table/index for each id?
Yes. You could load up a temp table (Emp_id, date) and then join the update to the temp table. It would lower the lookups by a 5th at the cost increasing the code complexity. For something that will run once, I think the clarity of the correlated sub-query overrides any benefit on time. Of course, if we are talking gigabyte tables, I take it all back.
-
PIEBALDconsult wrote:
Won't that rescan the table/index for each id?
Yes. You could load up a temp table (Emp_id, date) and then join the update to the temp table. It would lower the lookups by a 5th at the cost increasing the code complexity. For something that will run once, I think the clarity of the correlated sub-query overrides any benefit on time. Of course, if we are talking gigabyte tables, I take it all back.
But of more import is that we are talking about a beginner who got this response from an expert* -- that's the big problem in my opinion. The beginner should be shown better code because he won't know any better otherwise. The poster, or other inexperienced developers who happen by, may think that the provided code is good for all situations. * Anyone posting responses here assumes the role of expert with all the responsibilities thereof.
Michael Potter wrote:
increasing the code complexity
I don't think my solution is any more complex than the other solution.
Michael Potter wrote:
lower the lookups by a 5th
Correct me if I'm wrong (I could very well be), but my expectation of the first solution is n+1 lookups, whereas mine is 2 lookups. Granting that a modern database like SQL Server should work smarter than I would.
-
I need some help with an SQL query I’ve been asked to create. Due to lack of communication, an update was done incorrectly and now needs to be fixed (surprise, surprise :-D ). Each employee has 5 Ben_Codes and the start date for all Ben_Codes needs to be equal to the start date of Ben_Code 1. The dates are not constant so for each employee I need to read Ben_Code 1 record to retrieve start_date and then update Ben_Codes 2 thru 5 with that start_date. To this point I have done very basic updates using SQL Query and would appreciate any guidance you can provide!! Emp_id, Ben_Code, start_date 99999 1 10/12/12 99999 2 01/12/13 99999 3 01/12/13 99999 4 01/12/13 99999 5 01/12/13 98989 1 9/15/2012 98989 2 01/12/13 98989 3 01/12/13 98989 4 01/12/13 98989 5 01/12/13
Thank you all for the responses. I was able to create the SQL I needed based on all your replies. I opt'd to follow example provided by PIEBALD as I am new to writing SQL and figure I should learn the more efficient methodology and when I am more versed in SQL I can do the quick and "dirty" when I have too. Once again the help provided has been great and I really appreciate it! Michelle :-D
-
But of more import is that we are talking about a beginner who got this response from an expert* -- that's the big problem in my opinion. The beginner should be shown better code because he won't know any better otherwise. The poster, or other inexperienced developers who happen by, may think that the provided code is good for all situations. * Anyone posting responses here assumes the role of expert with all the responsibilities thereof.
Michael Potter wrote:
increasing the code complexity
I don't think my solution is any more complex than the other solution.
Michael Potter wrote:
lower the lookups by a 5th
Correct me if I'm wrong (I could very well be), but my expectation of the first solution is n+1 lookups, whereas mine is 2 lookups. Granting that a modern database like SQL Server should work smarter than I would.
-
PIEBALDconsult wrote:
may think that the provided code is good for all situations.
Very possible. But that is a different discussion and one that applies to many things in developement. And a code snippet will not teach that.
Yes, but it's very important on a site like this. And in the real world, too, not just development. If you're going to teach a man to fish, do it right. Sure, in a pinch you can use a shotgun, but you shouldn't be teaching newbies to fish with a shotgun.
-
Yes, but it's very important on a site like this. And in the real world, too, not just development. If you're going to teach a man to fish, do it right. Sure, in a pinch you can use a shotgun, but you shouldn't be teaching newbies to fish with a shotgun.
PIEBALDconsult wrote:
If you're going to teach a man to fish, do it right. Sure, in a pinch you can use a shotgun, but you shouldn't be teaching newbies to fish with a shotgun.
Now that really depends upon how hungry they are!
-
PIEBALDconsult wrote:
If you're going to teach a man to fish, do it right. Sure, in a pinch you can use a shotgun, but you shouldn't be teaching newbies to fish with a shotgun.
Now that really depends upon how hungry they are!
But you have to make it clear that this is for special cases only, not for every day.
-
Yes, but it's very important on a site like this. And in the real world, too, not just development. If you're going to teach a man to fish, do it right. Sure, in a pinch you can use a shotgun, but you shouldn't be teaching newbies to fish with a shotgun.
PIEBALDconsult wrote:
If you're going to teach a man to fish, do it right
The point here however is not how to fish. Rather it is how to use a spinner when trolling for big mouth bass at dawn around slow moving water amoungst rocks. Which is something that someone might need to know. But not without the qualification. And it is not teaching them how to fish.