应该是 update tmp1 set tmp1.id=tmp.id from tmp where ......
对不起,应该是这样 update tmp1 set tmp1.id=tmp.id from tmp where tmp1.name=tmp.name and tmp1.id in( select id from tmp )
FROM 子句中指定的表的别名不能作为 SET column_name 子句中的限定符使用。例如,下面的内容无效:UPDATE titles SET t.ytd_sales = t.ytd_sales + s.qty FROM titles t, sales s WHERE t.title_id = s.title_id AND s.ord_date = (SELECT MAX(sales.ord_date) FROM sales)若要使上例合法,请从列名中删除别名 t。UPDATE titles SET ytd_sales = t.ytd_sales + s.qty FROM titles t, sales s WHERE t.title_id = s.title_id AND s.ord_date = (SELECT MAX(sales.ord_date) FROM sales)
update tmp1 set tmp1.id=tmp.id
from tmp
where ......
update tmp1 set tmp1.id=tmp.id
from tmp
where tmp1.name=tmp.name and tmp1.id in(
select id
from tmp
)
SET t.ytd_sales = t.ytd_sales + s.qty
FROM titles t, sales s
WHERE t.title_id = s.title_id
AND s.ord_date = (SELECT MAX(sales.ord_date) FROM sales)若要使上例合法,请从列名中删除别名 t。UPDATE titles
SET ytd_sales = t.ytd_sales + s.qty
FROM titles t, sales s
WHERE t.title_id = s.title_id
AND s.ord_date = (SELECT MAX(sales.ord_date) FROM sales)