SQL update on multiple tables

[Falco Vermeer]

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

[David Portas]

Re: SQL update on multiple tables

The query you posted appears to be valid TSQL.
[color=blue]
> "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."[/color]

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

[Falco Vermeer]

Re: SQL update on multiple tables

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!

[Erland Sommarskog]

Re: SQL update on multiple tables

Falco Vermeer (f.vermeer@alte rnate.nl) writes:[color=blue]
> 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.[/color]

The statement you posted, cannot alone give that error message you
posted.
[color=blue]
> 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.[/color]

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, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp