|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
updating columnHow do i update a column to the similar column of a similar table with common
keys? Thanks. -- bic Untested (of course):
update tableA set tableA.columnA = (select tableB.columnA from tableB where tableB.keyColumn = tableA.keyColumn) Show quote "bic" <b**@discussions.microsoft.com> wrote in message news:B99A7A02-F753-4715-8EC8-94123D195730@microsoft.com... > How do i update a column to the similar column of a similar table with > common > keys? Thanks. > -- > bic Thanks for the suggestion, but with that query I am getting the following
Server: 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. How i can get around this is what i am asking. Thanks. -- Show quotebic "Raymond D'Anjou" wrote: > Untested (of course): > update tableA set tableA.columnA = (select tableB.columnA from tableB where > tableB.keyColumn = tableA.keyColumn) > > "bic" <b**@discussions.microsoft.com> wrote in message > news:B99A7A02-F753-4715-8EC8-94123D195730@microsoft.com... > > How do i update a column to the similar column of a similar table with > > common > > keys? Thanks. > > -- > > bic > > > Your UPDATE is returning more than one row in the subquery for some row
in the table being updated. What you need to do is change the UPDATE so that only one row is returned. Usually you'll want the columns being compared in the correlated subquery to be unique otherwise you have a problem with your spec - not with your SQL. If you need more help, please post some code to repro the problem: CREATE, INSERT and UPDATE statements. -- David Portas SQL Server MVP -- That's why I wrote "Untested".
Next time post DDL (create table statements) and sample insert statements. That way, we can give you a tested solution. Glad to see that Joe from WI gave you a working solution. If you have more than 1 matching row in tableB with different values in that column, from what row do you want to select? Show quote "bic" <b**@discussions.microsoft.com> wrote in message news:2D11398A-7CF0-45B0-8F14-9BF260BF8C13@microsoft.com... > Thanks for the suggestion, but with that query I am getting the following > > Server: 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. > > How i can get around this is what i am asking. Thanks. > -- > bic > > > "Raymond D'Anjou" wrote: > >> Untested (of course): >> update tableA set tableA.columnA = (select tableB.columnA from tableB >> where >> tableB.keyColumn = tableA.keyColumn) >> >> "bic" <b**@discussions.microsoft.com> wrote in message >> news:B99A7A02-F753-4715-8EC8-94123D195730@microsoft.com... >> > How do i update a column to the similar column of a similar table with >> > common >> > keys? Thanks. >> > -- >> > bic >> >> >> For an example, I copied the authors table in the pubs database to authors2.
This script will update similar tables and columns. If needed, you can specify a WHERE clause (following the FROM statement) to specify a conditional update. Otherwise, it updates all rows in the table where it could match keys. update [dbo].[authors] set au_lname = a2.au_lname from [dbo].[authors] as a1 inner join [dbo].[authors2] as a2 on a1.au_id = a2.au_id If I misunderstood, please reply with more details. Hope that helps, Joe Show quote "bic" wrote: > How do i update a column to the similar column of a similar table with common > keys? Thanks. > -- > bic It worked. Thanks very much, Joe.
-- Show quotebic "Joe from WI" wrote: > For an example, I copied the authors table in the pubs database to authors2. > This script will update similar tables and columns. If needed, you can > specify a WHERE clause (following the FROM statement) to specify a > conditional update. Otherwise, it updates all rows in the table where it > could match keys. > > update [dbo].[authors] > set au_lname = a2.au_lname > from [dbo].[authors] as a1 inner join [dbo].[authors2] as a2 on a1.au_id = > a2.au_id > > If I misunderstood, please reply with more details. > > Hope that helps, > Joe > > "bic" wrote: > > > How do i update a column to the similar column of a similar table with common > > keys? Thanks. > > -- > > bic bic wrote:
> It worked. Thanks very much, Joe. It may have *run*. That doesn't mean it *worked*. The trouble with this> -- > bic > > UPDATE syntax is that the result is undefined if the join isn't unique in the table which you are joining to. This is potentially very dangerous because you won't know what data has been updated. You won't know that anything was potentially inconsistent because the statement just silently goes ahead and does the update anyway! I strongly recommend you don't use the UPDATE ... FROM ... JOIN version unless and until you understand why your original update was wrong or until you've identified a unique join criteria. -- David Portas SQL Server MVP --
Show quote
"David Portas" <REMOVE_BEFORE_REPLYING_dpor***@acm.org> wrote in message Thanks David for giving the explanation as to WHY I asked him this question news:1132778568.223102.198490@g49g2000cwa.googlegroups.com... > bic wrote: >> It worked. Thanks very much, Joe. >> -- >> bic >> >> > > It may have *run*. That doesn't mean it *worked*. The trouble with this > UPDATE syntax is that the result is undefined if the join isn't unique > in the table which you are joining to. This is potentially very > dangerous because you won't know what data has been updated. You won't > know that anything was potentially inconsistent because the statement > just silently goes ahead and does the update anyway! > > I strongly recommend you don't use the UPDATE ... FROM ... JOIN version > unless and until you understand why your original update was wrong or > until you've identified a unique join criteria. in my last post: "If you have more than 1 matching row in tableB with different values in that column, from what row do you want to select?" That's why you're the MVP. Thanks for the info David.
The original question that I responded to was similar table with common keys. (I assumed a unique relationship...you know what happens when you assume...I'm still cleaning off my shoes...:)) I was in the mind set of updating child rows based on data in a parent table. For example, updating an order table with data in a customer table. (i.e. A single row as the source updating to one to many rows.) I didn't think of a many to many update with multiple, different values. If that can't be cleaned up, then I'd recommend a cursor so that you have more programatic control over what is being updated, log inconsistencies, etc. Joe Show quote "Raymond D'Anjou" wrote: > "David Portas" <REMOVE_BEFORE_REPLYING_dpor***@acm.org> wrote in message > news:1132778568.223102.198490@g49g2000cwa.googlegroups.com... > > bic wrote: > >> It worked. Thanks very much, Joe. > >> -- > >> bic > >> > >> > > > > It may have *run*. That doesn't mean it *worked*. The trouble with this > > UPDATE syntax is that the result is undefined if the join isn't unique > > in the table which you are joining to. This is potentially very > > dangerous because you won't know what data has been updated. You won't > > know that anything was potentially inconsistent because the statement > > just silently goes ahead and does the update anyway! > > > > I strongly recommend you don't use the UPDATE ... FROM ... JOIN version > > unless and until you understand why your original update was wrong or > > until you've identified a unique join criteria. > > Thanks David for giving the explanation as to WHY I asked him this question > in my last post: > "If you have more than 1 matching row in tableB with different values in > that column, from what row do you want to select?" > That's why you're the MVP. > > >  |
|||||||||||||||||||||||
Other interesting topics