Advance Update Statement Sql Server

[HeadScratcher]

I am trying to speed up my update statements by removing inner select
statements.

Example:
update orders set shipname = (select contactName from
customers where customerid = orders.customerID)

I read some articles which said that I should be able to use an inner
join on the update statement like the following:
update orders set shipname = (select contactName from customers where
customerid = orders.customerID)

But every time that I run this statement I get the follwing error:
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'inner'.

Any Help will be greatly appreciated.

Thank you.

[David Portas]

I think you posted the wrong UPDATE statement. Both those statements
are identical and valid syntax.

However, if performance is your concern then why not take SHIPNAME out
of the Orders table. It looks like it's redundant there.

--
David Portas
SQL Server MVP
--

[Simon Hayes]

See "Changing Data Using the FROM Clause" and also example C under
UPDATE in Books Online (although it would be better to rewrite it with
INNER JOIN).

Simon

[--CELKO--]

Both statements were the same ?! I think I know what you meant to say
...

As long as the scalar query expression returns zero or one row, you
will be fine. If you use the proprietary FROM syntax, you will get an
unpredictable result from a multi-row result set.

The real cost of an update is in the physical disk access, not the
code.

[Erland Sommarskog]

HeadScratcher (ma***@servicemg.com) writes:

I am trying to speed up my update statements by removing inner select
statements.

Example:
update orders set shipname = (select contactName from
customers where customerid = orders.customerID)

I read some articles which said that I should be able to use an inner
join on the update statement like the following:

update orders set shipname = (select contactName from customers where
customerid = orders.customerID)

But every time that I run this statement I get the follwing error:
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'inner'.

Apparently there was some glitch in the editing. Anyway, this is what
you want:

UPDATE Orders
SET ShipName = c.ContactName
FROM Orders o
JOIN Customers c ON c.CustomerID = O.CustomerID

I suspect the problem is that you left out the FROM clause.

I left out INNER here, because this is implied.

I should add your original syntax is in alignment with ANSI standards,
whereas the syntax with FROM JOIN is proprietary to MS SQL Server and
Sybase (and possibly Informix). If you need portability, stick to the
original syntax. As long as you work with SQL Server only, do as you
please. Personally, I find the FROM/JOIN syntax very pleasant, as it
builds on the same paradigm as a regular SELECT statement. It is also
more effecient, if you need to update more than one column.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Erland Sommarskog]

David Portas (RE****************************@acm.org) writes:

However, if performance is your concern then why not take SHIPNAME out
of the Orders table. It looks like it's redundant there.

Nah, I would not recommend people to drop columns from their
Northwind databases. :-)
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[--CELKO--]

>> I find the FROM/JOIN syntax very pleasant, as it builds on the same paradigm as a regular SELECT statement.<<

Arrrgh! The **consistent meaning in the Standard SQL model** of a
FROM clause is that a temporary working table is constructed, used and
dropped at the end of the statement. You would be updating a temporary
working table, not the base table.

The Sybase, Informix and MS SQL Server syntax might look the same, but
the semantics are all slightly different when you get to a 1:m
relationship. Moving the code is deadly -- it moves over to the next
platform and runs differently. With the ANSI syntax, the vendors have
to follow the same rules. This is a good thing.