Update using subquery help!
-
Hi all,
I'm trying to update a table using a subquery. when I run the below script I get an error Msg 512. I have done updates like this in the pass that worked but for some reason it's throwing an error. can anyone help please.
UPDATE dbo.Master_sub
SET dbo.Master_sub.[Lab Count] = (SELECT dbo.o_list.[ Lab Count]
FROM dbo.o_list
WHERE dbo.Master_sub.[Original ID] = dbo.o_list.[ID])
WHERE EXISTS
(SELECT dbo.o_list[Lab Count]
FROM dbo.o_list
WHERE dbo.Master_sub.[Original ID] = dbo.o_list.[ID])
Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.
This one works!!!!!
UPDATE remaster
SET [recheck] = ( SELECT [ML].[recheck]
FROM ml
WHERE [ML].[Primary Key] = [remaster].[Primary Key])
WHERE EXISTS
(SELECT [ML].[recheck]
FROM ml
WHERE [ML].[Primary Key] =[remaster].[Primary Key])
-
bighow2008 (5/15/2012)
Hi all,I'm trying to update a table using a subquery. when I run the below script I get an error Msg 512. I have done updates like this in the pass that worked but for some reason it's throwing an error. can anyone help please.
UPDATE dbo.Master_sub
SET dbo.Master_sub.[Lab Count] = (SELECT dbo.o_list.[ Lab Count]
FROM dbo.o_list
WHERE dbo.Master_sub.[Original ID] = dbo.o_list.[ID])
WHERE EXISTS
(SELECT dbo.o_list[Lab Count]
FROM dbo.o_list
WHERE dbo.Master_sub.[Original ID] = dbo.o_list.[ID])
Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.
This one works!!!!!
UPDATE remaster
SET [recheck] = ( SELECT [ML].[recheck]
FROM ml
WHERE [ML].[Primary Key] = [remaster].[Primary Key])
WHERE EXISTS
(SELECT [ML].[recheck]
FROM ml
WHERE [ML].[Primary Key] =[remaster].[Primary Key])
The error message is pretty self explanatory here.
Your issue lies in this:
SET dbo.Master_sub.[Lab Count] = (SELECT dbo.o_list.[ Lab Count]
FROM dbo.o_list
WHERE dbo.Master_sub.[Original ID] = dbo.o_list.[ID])
This subquery is returning more than 1 row so the engine doesn't know which one to pick for your update. You need to figure out how to make that query return 1 row. I don't know your data so I don't know what the best approach might be. (distinct, top 1, better filtering, etc)
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
Thanks for the reply.
I went with a simpleir query
update master_sub
set [lab count] = o_list.[lab count],
= o_list.
from master_sub m, o_list o
where m.id = o.id
-
That would be another way. 😀 Nicely done!!!
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
bighow2008 (5/15/2012)
Thanks for the reply.I went with a simpleir query
update master_sub
set [lab count] = o_list.[lab count],
= o_list.
from master_sub m, o_list o
where m.id = o.id
Just one little thing, and it really is cosmetic in this case. You should write your T-SQL code using ANSI-92 style joins instead of ANSI-89 style joins. It actually makes reading the code easier as it separates the join criteria between tables from any filter criteria you may have on the query. In this insance, you would not have a WHERE clause as part of your update.
update master_sub set
[lab count] = o_list.[lab count],
= o_list.
from
master_sub m
inner join o_list o
on (m.id = o.id);
-
CELKO (5/15/2012)
>> I'm trying to update a table using a subquery. when I run the below script I get an error Msg 512. I have done updates like this in the pass that worked but for some reason it's throwing an error. can anyone help please. <<Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. If you know how, follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect.
This is minimal polite behavior on SQL forums.
Sample data is also a good idea, along with clear specifications. It is very hard to debug code when you do not let us see it. If you want to learn how to ask a question on a Newsgroup, look at: http://www.catb.org/~esr/faqs/smart-questions.html
Your past behavior was bad programming but typical of T-SQL dialect programmers. Today, you can do it right with the MERGE statement. I also hope that what youi did post is a skeleton and not the garbage it looks like. Embedded blanks, reserved words and non-relational stuff everywhere. You still use the name Master? Like a mag tape file or a 1970's network DB? Magical generic “id”?
All we can do it guess at an answer. Let's change the data element names. In RDBMS, we do not use sequential structures like lists; that was a mag tape and not a table. A count (of what?) is computed and not stored in most good schema designs. Here is a skeleton statement, with better names
MERGE INTO Foobar
USING Scratch_Tape
ON Foobar.foobar_id = Scratch_Tape.foobar_id
WHEN MATCHED
THEN UPDATE
SET lab_something_cnt = Scratch_Tape.lab_something_cnt;
The old proprietary UPDATE.. FROM.. is wrong; it gives you bad data. You need to do a full data audit on the database. You need to fire the incompetent that did this to you. You need to re-do the schema.
Once again, you and your high horse ride in. Sorry if you don't like using proprietary code, but this is MS SQL Server and I haven't seen anything indicating that Microsoft was depreciating anything in their version of the UPDATE statement.
So, with that I will continue to use the T-SQL UPDATE statement as Microsoft allows us to.
Now, please change your attitude or just go away.
-
Heh... easy, Lynn. He's not going to change. Consider how long he's been wearing the same shirt and vest! 😀
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
He also has a valid point here. We already know that there was a problem with multiple records being returned and both the subquery and the merge will raise errors in that case, but the UPDATE...FROM does not. It's better to clean up the error than to sweep it under the rug by using the form that doesn't report a known error.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA -
CELKO (5/16/2012)
>> Once again, you and your high horse ride in. <<Gee, ANSI/ISO Standards, etc are "high horse" to you? Does that say a lot about your professionalism and expertise as a database programmer?
>> Sorry if you don't like using proprietary code, but this is MS SQL Server and I haven't seen anything indicating that Microsoft was depreciating anything in their version of the UPDATE statement. <<
Might want to spend a little time in the SQL Server community, even if you are not aware of the data community. The best article on this issue is "Let's deprecate UPDATE FROM!" by Hugo Kornelis.. but it is not the only voice http://sqlblog.com/blogs/hugo_kornelis/archive/2008/03/10/lets-deprecate-update-from.aspx
>> So, with that I will continue to use the T-SQL UPDATE statement as Microsoft allows us to. <<
Do you also set the switches to allow the *= syntax? You know how dangerous it is, how much MVPs want to get rid of it and that it does not port. But you still want to use it? And you think this is how a competent professional behaves with a client?
>> Now, please change your attitude or just go away. <
What a carefully reasoned argument 🙂 Why don't you stick around and learn to code SQL properly?
Gee, ANSI/ISO Standards, etc are "high horse" to you? Does that say a lot about your professionalism and expertise as a database programmer?
No, ANSI/ISO standards aren't a "high horse", you are the way you come spouting standards and not really trying to understand what the OP's problem is and try to actually help solve the problem.
Might want to spend a little time in the SQL Server community, even if you are not aware of the data community. The best article on this issue is "Let's deprecate UPDATE FROM!" by Hugo Kornelis.. but it is not the only voice http://sqlblog.com/blogs/hugo_kornelis/archive/2008/03/10/lets-deprecate-update-from.aspx
Well, sorry if I haven't read what everyone writes. Unfortunately, I find the way that T-SQL works feels right when compared to the ANSI Standard as implemented in Oracle. I felt like I had to jump through unnecessary hoops to write updates in Oracle that are more easily done in T-SQL with the UPDATE FROM structure.
Do you also set the switches to allow the *= syntax? You know how dangerous it is, how much MVPs want to get rid of it and that it does not port. But you still want to use it? And you think this is how a competent professional behaves with a client?
To be honest I HATE the ANSI-89 style joins. When I started working with MS SQL Server 6.5 I started using the ANSI-92 style joins from the start. Makes writing T-SQL code easier and more readable. Separates the JOIN criteria between tables from the FILTER criteria in the WHERE clause.
By the way, a competent professional doesn't degrade others the way you do on a constant basis. Your books may be very good, but because of your attitude on these forums I will never read them nor recommend them to anyone. I would rather listen to people that actually try to help others to learn how to do things with the tools available. If that means writing code that is specific to MS SQL Server because it is more performant than writing strictly standard sql code that could be ported unchanged to MySQL or Oracle, or PostgreSQL, or InterBase, or any other RDBMS then so be it. How often do companies change RDBMS's, it sure isn't every year or every other year.
Anything else you would like to say?
-
This is getting good.
I understand both sides of this argument. Now sure if there is an correct answer. 😉
-
CELKO (5/16/2012)
>> .. not really trying to understand what the OP's problem is and try to actually help solve the problem. <<Unh? I am the guy who tries to understand the problem in depth and NOT spit out a kludge. The OP now has enough info for him to research and learn. Gripe about me being boring and pedantic.
>> Well, sorry if I haven't read what everyone writes. <<
Hugo Kornelis is one of the big names in the community, not just a “someone”; most of his suggestions have been implemented by Conner and crew over the years. He does the research, publishes good stuff and one of the people I think is smart. Pick who you read.
>> Unfortunately, I find the way that T-SQL works feels right when compared to the ANSI Standard as implemented in Oracle. <<
Oracle sucks! It has more dialect than T-SQL. DB2 is the best of the Big Three for my money.
>> I felt like I had to jump through unnecessary hoops to write updates in Oracle that are more easily done in T-SQL with the UPDATE FROM structure. <<
Oracle has the MERGE statement. It was a joint effort by Oracle and IBM. I acfrually like some of the T-SQL extensions and hope we get the DELETE cl;ause.
>> To be honest I HATE the ANSI-89 style joins. When I started working with MS SQL Server 6.5 I started using the ANSI-92 style joins from the start. Makes writing T-SQL code easier and more readable. Separates the JOIN criteria between tables from the FILTER criteria in the WHERE clause. <<
There is good story about the infixed join syntax. I prefer the original syntax for INNER JOINs because I grew up with it and I think in sets. People with a sequential mindset use the infixed notation jut as they use + ; people with a set-oriented use the original notation for the same reason they use S (big sigma).
>> If that means writing code that is specific to MS SQL Server because it is more performant than writing strictly standard SQL code that could be ported unchanged to MySQL or Oracle, or PostgreSQL, or InterBase, or any other RDBMS then so be it. How often do companies change RDBMS's, it sure isn't every year or every other year. <<
|ANSI/ISO Standards are on a five year cycle; so are my books for that reason. By the time that some feature gets into the specs, boys in the lab have worked out the algorithms, done prototypes and talked to each other. The Standard SQL is most often the performant code in a product.
How do you feel about MS now urging programmers to go back to ODBC instead of their proprietary stuff?
>>>> .. not really trying to understand what the OP's problem is and try to actually help solve the problem. <<
>>Unh? I am the guy who tries to understand the problem in depth and NOT spit out a kludge. The OP now has enough info for him to research and learn. Gripe about me being boring and pedantic.
How do you try to understand an OP's problem? You go an your boiler plated rant about ANSI/ISO standards, you tell the OP how he doesn't understand RDBMS's becuase he happens to use the terms record and field instead of row and column, etc. Other than informing the OP that we need DDL , sample data, expected results, you don't take any time to ask questions that may help in understanding the problem. I have rarely even see you come back to any of the threads that you have posted on to see how the OP is doing and if they need more help, nor have I seen you trying to help anyone to understand what they should be trying to do or even trying to show different ways to solve a possible problem. You ride in, talk down to the OP, then ride off again.
>>>> Well, sorry if I haven't read what everyone writes. <<
>>Hugo Kornelis is one of the big names in the community, not just a “someone”; most of his suggestions have been implemented by Conner and crew over the years. He does the research, publishes good stuff and one of the people I think is smart. Pick who you read.
Well, I have read his posts here on SSC, and yes, he is intelligent and thoughtful. He if trys to be genuinely helpful to those willing to learn. I have even picked up some things from him. I also know there are some things that he and I don't necessarily see eye to eye on, but at least he is willing to agree to disagree. I don't get that from you at all. From my perspective it is your way or the highway.
>>>> Unfortunately, I find the way that T-SQL works feels right when compared to the ANSI Standard as implemented in Oracle. <<
>>Oracle sucks! It has more dialect than T-SQL. DB2 is the best of the Big Three for my money.
I haven't worked with DB2 so I have no opinion here, sorry.
>>>> I felt like I had to jump through unnecessary hoops to write updates in Oracle that are more easily done in T-SQL with the UPDATE FROM structure. <<
>>Oracle has the MERGE statement. It was a joint effort by Oracle and IBM. I acfrually like some of the T-SQL extensions and hope we get the DELETE cl;ause.
Are you saying we should all dump INSERT, UPDATE, and DELETE and use only the MERGE statement? I'm sure with thought that it could be done, but seems like a lot of extra work that doesn't need to be done.
>>>> To be honest I HATE the ANSI-89 style joins. When I started working with MS SQL Server 6.5 I started using the ANSI-92 style joins from the start. Makes writing T-SQL code easier and more readable. Separates the JOIN criteria between tables from the FILTER criteria in the WHERE clause. <<
>>There is good story about the infixed join syntax. I prefer the original syntax for INNER JOINs because I grew up with it and I think in sets. People with a sequential mindset use the infixed notation jut as they use + ; people with a set-oriented use the original notation for the same reason they use S (big sigma).
I can think in sets without having to use the old style. I like breaking things down in pieces. I identify the criteria needed to join the tables together to generate the necessary result set, that criteria goes in the FROM clause with the tables. Next, identify just the data you need by identifying the filter conditions needed to reduce the big set to just what is needed. That goes in the WHERE clause. If there is a structure change to the tables that changes how they are joined, you only have to make changes in the FROM clause.
>>>> If that means writing code that is specific to MS SQL Server because it is more performant than writing strictly standard SQL code that could be ported unchanged to MySQL or Oracle, or PostgreSQL, or InterBase, or any other RDBMS then so be it. How often do companies change RDBMS's, it sure isn't every year or every other year. <<
>>|ANSI/ISO Standards are on a five year cycle; so are my books for that reason. By the time that some feature gets into the specs, boys in the lab have worked out the algorithms, done prototypes and talked to each other. The Standard SQL is most often the performant code in a product.
Every Database Vendor has included proprietary features in their systems, just like vendors have done in many of the computer languages that have been used since the early 60's (and maybe before), and these features have allowed developers to write code that works closer to the system than the standard. Yes, the code isn't portable, but I am one of those that doesn't believe in the fallacy of 100% portable code. Businesses don't change systems as often as people change socks. So if I can write code using the features provided by the vendor, even if it isn't standard, and it runs better than code written to the standard, guess what I am writing.
>>How do you feel about MS now urging programmers to go back to ODBC instead of their proprietary stuff?
Being stricly a Database Admin/Developer, not much. Microsoft (and other vendors) are notorious for changing directions. We either adopt and adapt or we don't work. If they can abstract the interface so that I don't have to worry about wether I am using ODBC or OLEDB, I would be happy.
-
CELKO (5/16/2012)
The best article on this issue is "Let's deprecate UPDATE FROM!" by Hugo Kornelis.. but it is not the only voice http://sqlblog.com/blogs/hugo_kornelis/archive/2008/03/10/lets-deprecate-update-from.aspx%5B/quote%5DGreat link, thanks for posting this. I hadn't thought about the problem with UPDATE..FROM repeatedly modifying the same row, if the JOIN returned multiple matching rows.
Rich
-
CELKO (5/16/2012)
...The kids I tutor at a failing middle school ...
Poor kids... :w00t:
I only can guess which first book you make them to read, it must be ISO standards. Following by the list of yours published by...
Should they memorise them all, otherwise they get caned or strapped?
http://www.west-dunbarton.gov.uk/mww/html/punishment.html
I can think of using one of the above book as a "The Punishment Book"...
:hehe:
-
CELKO (5/16/2012)
>> Once again, you and your high horse ride in. <<Gee, ANSI/ISO Standards, etc are "high horse" to you? Does that say a lot about your professionalism and expertise as a database programmer?
>> Sorry if you don't like using proprietary code, but this is MS SQL Server and I haven't seen anything indicating that Microsoft was depreciating anything in their version of the UPDATE statement. <<
Might want to spend a little time in the SQL Server community, even if you are not aware of the data community. The best article on this issue is "Let's deprecate UPDATE FROM!" by Hugo Kornelis.. but it is not the only voice http://sqlblog.com/blogs/hugo_kornelis/archive/2008/03/10/lets-deprecate-update-from.aspx
>> So, with that I will continue to use the T-SQL UPDATE statement as Microsoft allows us to. <<
Do you also set the switches to allow the *= syntax? You know how dangerous it is, how much MVPs want to get rid of it and that it does not port. But you still want to use it? And you think this is how a competent professional behaves with a client?
>> Now, please change your attitude or just go away. <
What a carefully reasoned argument 🙂 Why don't you stick around and learn to code SQL properly?
The connect issue is here
http://connect.microsoft.com/SQLServer/feedback/details/332437/deprecate-update-from-and-delete-from
It's been closed by Microsoft as 'won't fix'. Looking at the comments, there didn't seem to be much support for it anyway.
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
My two bits:
Lynn, I strongly suspect Joe thrives on getting reactions to his outrageous language and treatment of posters (reminds me of Rush Limbaugh). And it is doubly frustrating because of his credibility. If he has a conscience or compassion, I certainly don’t see it in his posts and you have to ask yourself what kind of person he is to take so much pleasure in demeaning others. Can’t help his book sales much.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply