update from ...?

[rob]

Hey,

I need to update a row in one table with a row (most but not all
columns) from another table. This sounds like there should be a really
easy way to do this. I've seen there's FROM in the UPDATE statement but
haven't found any similar examples.

Basically, I need something like this pseudo-code:

update table2 set something = table1.somethin g (??)
from table1
where table2.id = 150

but, i only need some of the columns from table1 into table2.

I've seen things like:

update table2 set
table2.col1 =
(select table1.col1 where table1.id = 10),
table2.col2 =
(select table1.col2 where table1.id = 10)
where table2.id = 10

....but this seems really verbose, because there's like a dozen columns.

Even a link to a tutorial is welcome, thanks!

:rob

[Brock]

Re: update from ...?

Try this:

update table2
set a.col1 = b.col1, a.col2 = b.col2, etc..
from table2 a
inner join table1 b on a.id = b.id
where a.id = 10

[Little PussyCat]

Re: update from ...?

[color=blue]
> update table2 set
> table2.col1 =
> (select table1.col1 where table1.id = 10),
> table2.col2 =
> (select table1.col2 where table1.id = 10)
> where table2.id = 10[/color]

Try this

UPDATE table2
SET table2.ColumnNa me=Table1.Colum nName, table2.columnNa meB=table1.Colu mnB
FROM Table1 INNER JOIN table2 ON table1.PrimaryK ey=table2.Prima ryKey

When you want to update several columns just separate them with a comma like
shown, then join the two tables together in the FROM clause

Let me know how you get on,

Jayne

[--CELKO--]

Re: update from ...?

The answer is that it is verbose, but it works. The UPDATE..FROM
syntax is both proprietary and dangerous. This is a simple example
from Adam Machanic

CREATE TABLE Foo
(col_a CHAR(1) NOT NULL,
col_b INTEGER NOT NULL);

INSERT INTO Foo VALUES ('A', 0);
INSERT INTO Foo VALUES ('B', 0);
INSERT INTO Foo VALUES ('C', 0);

CREATE TABLE Bar
(col_a CHAR(1) NOT NULL,
col_b INTEGER NOT NULL);

INSERT INTO Bar VALUES ('A', 1);
INSERT INTO Bar VALUES ('A', 2);
INSERT INTO Bar VALUES ('B', 1);
INSERT INTO Bar VALUES ('C', 1);

You run this proprietary UPDATE with a FROM clause:

UPDATE Foo
SET Foo.col_b = Bar.col_b
FROM Foo INNER JOIN Bar
ON Foo.col_a = Bar.col_a;

The result of the update cannot be determined. The value of the column
will depend upon either order of insertion, (if there are no clustered
indexes present), or on order of clustering (but only if the cluster
isn't fragmented).

The best answer is for Microsoft to finally give us Standard SQL row
constructors in SQL-2005 so we could write:

UPDATE Foobar
SET (a,b,c, ..) = (SELECT x,y,z, .. FROM Barfoo WHERE ...);
WHERE ...;

[Erland Sommarskog]

Re: update from ...?

--CELKO-- (jcelko212@eart hlink.net) writes:[color=blue]
> The answer is that it is verbose, but it works. The UPDATE..FROM
> syntax is both proprietary and dangerous.[/color]

Anything is dangerous. I've seen people wrong with the ANSI syntax as
well. I'd say it's even easier to go wrong with, because it much more
difficult to understand.

The purported problem with UPDATE FROM potentially qualifying multiple
rows is a red herring. If you feel compelled to write:

UPDATE tbl
SET col = (SELECT ...

You shouldn't use FROM in a SELECT either, or at least for one
table:

SELECT col = (SELECT ...
FROM tbl

If you join with incomplete conditions, you could get multiple rows
and cause a mess.

There is a nice beauty in the FROM clause for UPDATE: it makes the
language simpler to use, because the same construct is reused.

And this newsgroup is about SQL Server, and when it comes to SQL
Server, FROM is good practice, because it often gives much better
performance than the other syntax.


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

[--CELKO--]

Re: update from ...?

>> If you join with incomplete conditions, you could get multiple rows
and cause a mess. <<

Actually, you get a cardinality violation error and the process stops.
You know whenyou have a problem when you use the proper syntax.
[color=blue][color=green]
>>And this newsgroup is about SQL Server, and when it comes to SQL[/color][/color]
Server, FROM is good practice, because it often gives much better
performance than the other syntax. <<

I can get great performance if i do not have to get the right answer,
port or maintain code :)

[Erland Sommarskog]

Re: update from ...?

--CELKO-- (jcelko212@eart hlink.net) writes:[color=blue][color=green][color=darkred]
>>> If you join with incomplete conditions, you could get multiple rows[/color][/color]
> and cause a mess. <<
>
> Actually, you get a cardinality violation error and the process stops.
> You know whenyou have a problem when you use the proper syntax.[/color]

You don't. I was talking about:

SELECT ...
FROM ...
JOIN ...

That is the twin to the UPDATE FROM statement.

Sure, with a plain SELECT you may notice that you have too many rows,
but if the missing condition is on a 1-to-1.10 relationship you may
not see it.

And if you aggregate, for instance SUM or COUNT(*) you ge the same
number of rows as with the correct join. You just get incorrect
results.

Thus, if FROM is dangerous for UPDATE, it's dangerous for SELECT as
well.


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