CODEWITHSUNDEEP
sqlsql-server-2008
RHD
RHD

Reputation: 15

SQL Server 2008: update date from row in same column

I've seen this question a lot, but I can't seem to get my SQL to work so I'm hoping someone can help. I've confirmed that this code will work in Access, but it isn't working with SQL Server 2008.

UPDATE shop_orders AS t1, shop_orders AS t2 
SET t1.shipstreet1 = t2.shipstreet1,
    t1.shipstreet2 = t2.shipstreet2, 
    t1.shipcity = t2.shipcity, 
    t1.shipregionstate = t2.shipregionstate
WHERE t1.orderid=3292 
  AND t2.orderid=3641;

Advice?

Upvotes: 1

Views: 598

Answers (2)

marc_s
marc_s

Reputation: 754220

In SQL Server's T-SQL, you cannot have multiple tables in the UPDATE clause, nor can you give that table a table alias.

You need to use:

UPDATE shop_orders 
FROM shop_orders AS t2 
SET shipstreet1 = t2.shipstreet1,
    shipstreet2 = t2.shipstreet2, 
    shipcity = t2.shipcity, 
    shipregionstate = t2.shipregionstate
WHERE shop_orders.orderid = 3292 
  AND t2.orderid = 3641;

Upvotes: 3

keithwill
keithwill

Reputation: 2044

The syntax for Update From is different for SQL server, the same query will not work in both.

See: SQL update from one Table to another based on a ID match

For more information on Update From syntax.

Upvotes: 0

Related Questions