473,574 Members | 2,916 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 70108
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
4987
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
2989
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...
9
12958
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
1562
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...
1
3089
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
4668
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
2079
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...
10
3281
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...
4
2343
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...
5
4067
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...
0
7835
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...
0
8095
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. ...
0
6500
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...
1
5645
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
5332
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...
0
3769
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...
0
3787
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
2265
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
0
1096
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...

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.