473,738 Members | 7,599 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

SQL update on multiple tables

Hi,

I tried to use the following query to update a value in one table with
a value from another table:

UPDATE tbl1
SET col1 = tbl2.col2
FROM tbl1, tbl2
WHERE tbl1.[id] = tbl2.[id]

but it won't work. I also tried this with a subquery using "TOP 1",
but that wouldn't work either. SQL Server 2000 gives me the following
error-message:

"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."

What is wrong? It seems that the join is not functioning properly, but
it functions properly with a rather 'simple' SELECT. How to update
those fields, other than by hand ;)

Regards,

Falco Vermeer
Jul 20 '05 #1
3 70116
The query you posted appears to be valid TSQL.
"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."


Do you get that error message from the UPDATE statement you posted or from
some other UPDATE statement? (you aren't quite clear on that point) If you
get that error from the statement you posted then I suspect the problem is
with a trigger since that statement doesn't include a subquery. Check any
trigger code you have on that table.

If you don't get that error message then what does "won't work" mean.

Note also that ANSI/ISO Standard SQL doesn't allow joins in an UPDATE
statement, only subqueries. The Standard SQL version of the statement you
posted is as follows:

UPDATE tbl1
SET col1 =
(SELECT col2
FROM tbl2
WHERE tbl1.[id] = tbl2.[id])

This assumes that ID is unique in Tbl2, otherwise you will get the error you
mentioned whereas the proprietary UPDATE syntax you posted will fail to
detect that logical error and go ahead by updating your table in an
indeterminate manner that may not be what you expect. I always recommend
sticking to the "safer" ANSI syntax where possible.

--
David Portas
SQL Server MVP
--
Jul 20 '05 #2
I get that error-message from the real UPDATE-statement, but I replaced
the tables with dummy-tables, so it's only reflecting the actual
situation and not showing any correct fieldnames.

There are more records in tbl1 than in tbl2. I also tried

UPDATE tbl1
SET col1 =
(SELECT col2
FROM tbl2
WHERE tbl1.[id] = tbl2.[id]) WHERE EXISTS
(SELECT col2
FROM tbl2
WHERE tbl1.[id] = tbl2.[id])

but that gives me the same error message ("Subquery returned more than 1
value."). I think one of those methods should work, but they fail both.
I have got triggers on that table, and I am going to check them, but
those triggers always worked correctly. I have no clue why they should
fail with this query.

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!
Jul 20 '05 #3
Falco Vermeer (f.*******@alte rnate.nl) writes:
I get that error-message from the real UPDATE-statement, but I replaced
the tables with dummy-tables, so it's only reflecting the actual
situation and not showing any correct fieldnames.
The statement you posted, cannot alone give that error message you
posted.
but that gives me the same error message ("Subquery returned more than 1
value."). I think one of those methods should work, but they fail both.
I have got triggers on that table, and I am going to check them, but
those triggers always worked correctly. I have no clue why they should
fail with this query.


Maybe the triggers were not written to handle multi-row updates?

Also, if you run your update from Query Analyzer, the error message
should contain where the error occurs. That is, if the occurs in a
trigger, the trigger name will be in the message, and so will the
line number of the statement that is causing the problem.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

17
5025
by: kalamos | last post by:
This statement fails update ded_temp a set a.balance = (select sum(b.ln_amt) from ded_temp b where a.cust_no = b.cust_no and a.ded_type_cd = b.ded_type_cd and a.chk_no = b.chk_no group by cust_no, ded_type_cd, chk_no)
1
2994
by: Wing | last post by:
Hi all, I have created 2 tables in sql database and join these 2 tables before assign the result to the dataset, and display the result in datagrid. Everything is fine up to this point. The problem come up when I want to delete one of rows in datagrid and update the change to the corresponding table. The error msg show up "Dynamic SQL generation is not supported against multiple base tables."
9
12986
by: jaYPee | last post by:
I have search a lot of thread in google newsgroup and read a lot of articles but still i don't know how to update the dataset that has 3 tables. my 3 tables looks like the 3 tables from northwind database that has an employees, orders, and order details. the following are the 3 tables in my sql database students schyrsem
1
1566
by: Foef | last post by:
When I have a stored procedure, with multiple tables in MS Access, in a dataset and I change it in my DataGrid, I get the next message when I want to update my DataSet: InvalidOperationException: "Dynamic SQL generation is not supported against a SelectCommand that does not return any base table information." I tried to use an update query but when I fill my DataTable using myDataAdapter.Fill(myDataSet, myTable)
1
3100
by: CS | last post by:
I need to update multiple tables from one form using a command button. The info from the different text boxes should then go into the tables. Can someone help me with some example code on how to do this.
6
4676
by: shil | last post by:
Hi, I am writing a windows app in .net 2003. I have a datagrid which gets data from a storedprocedure. My question is how can I update the data in the datagrid? I want to call another storedprocedure to update the data in the datagrid. Thanks in advance.
1
2089
by: andrewcw | last post by:
I have just 1 table that I am updating, the SQL I use to generate the DataTable is complex using multiple tables, however when I view the fieldnames of the DataTable from the DataSet - everything matches: What dont I understand here: ( The error I get is : Dynamic SQL generation is not supported against multiple base tables ). But My DataSet is 1 Table, and my target is 1 table, Why the error ?? Thanks : try {
10
3296
by: frizzle | last post by:
Hi there, I'm building a music site with a mysql backend. It has a many to many relational database. I use this to match music genres with certain artists, to maintain the possibility to add multiple genres to a singe artist. Now i've searched google, but can't find a solution on how to update rows with checkboxes. If an artist gets his genre updated as follows:
4
2346
by: dstorms | last post by:
I'm trying to run an update query on multiple tables, and since Access doesn't allow me to update tables from a union query, I'm writing a module as a workaround. So I've set up a temporary recordest (rstName) from the union query (qryEqiupEmplOnly), and construced a Do-Loop that updates the corresponding table. Unfortunately the Update SQL code prompts a compile error "Expected End of statement" and highlights "rstName" (line 28) as the...
5
4079
by: Bogdan | last post by:
Hi, I have a stored procedure that uses JOINs to return columns from multiple tables. I also have another stored proc that that takes a series of params and updates multiple tables. I used the framework to auto-generate a table adapter specifying both stored procs as Get/Fill and Update. The problem is that columns from the JOINed table seem to marked as 'read-only' so trying to update a row results in an exception. BTW, by default a...
0
8968
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
8787
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9473
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
9208
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
8208
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
6053
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4569
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
2
2744
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
2193
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.