SQL Server 2008: update date from row in same column

Question

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?

marc_s · Accepted Answer · 2011-08-18T14:22:48.653

3

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;

edited Aug 18 '11 at 14:22

answered Aug 18 '11 at 14:17

marc_s

732,580
175
1,330
1,459

1

You can give the table an alias if you include it in the `FROM` clause (and in fact, kind of need to if you're doing a self join in the `FROM`) – Damien_The_Unbeliever Aug 18 '11 at 14:25
@Damien_The_Unbeliever: correct - but not really needed here, that's why I skipped that – marc_s Aug 18 '11 at 14:26

score 0 · Answer 2 · edited May 23 '17 at 12:19

0

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.

edited May 23 '17 at 12:19

Community

1
1

answered Aug 18 '11 at 14:18

keithwill

1,964
1
17
26

SQL Server 2008: update date from row in same column

2 Answers2