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.

UPDA开发者_运维知识库TE 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?

---

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;

---

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.