改成这样怎么样? UPDATE table1 SET x = t1.a from (SELECT z,min(y) as a from table2 where table1.z=table2.z group by z) t1 where table1.z=t1.z
to supsuccess: 可以,但子查询写的有误: UPDATE table1 SET x = t1.a from (SELECT table1.z,min(table2.y) as a from table1,table2 where table1.z=table2.z group by table1.z) t1 where table1.z=t1.z
UPDATE table1 SET x =(select top 1 y form table2 where table1.z=table2.z)
UPDATE table1 SET x =(select top 1 y form table2 where table1.z=table2.z order by y desc)
你的语句没错,提高性能的关键是对table2中的z字段作索引。 需要注意的是如果table1中有不满足table1.z=table2.z的记录,那么这些记录中的x字段的值将被置为NULL。如果你不想更新这些记录,可以这样写: UPDATE table1 SET x = (SELECT min(y) from table2 where table1.z=table2.z) WHERE z in (select z from table2) 不过这个语句的性能不太好。可以用临时表解决这个问题: select z,min(y) as m into #tmp from table2 update table1 set x=b.m from table1 a,#tmp1 b where a.z=b.z
你的语句没错,提高性能的关键是对table2中的z字段作索引。 需要注意的是如果table1中有不满足table1.z=table2.z的记录,那么这些记录中的x字段的值将被置为NULL。如果你不想更新这些记录,可以这样写: UPDATE table1 SET x = (SELECT min(y) FROM table2 WHERE table1.z=table2.z) WHERE z IN (SELECT z FROM table2) 不过这个语句的性能不太好。可以用临时表解决这个问题: SELECT z,min(y) AS m INTO #tmp FROM table2 UPDATE table1 set x=b.m FROM table1 a,#tmp b WHERE a.z=b.z
[更新] 你的语句没错,提高性能的关键是对table2中的z字段作索引。 需要注意的是如果table1中有不满足table1.z=table2.z的记录,那么这些记录中的x字段的值将被置为NULL。如果你不想更新这些记录,可以这样写: UPDATE table1 SET x = (SELECT min(y) FROM table2 WHERE table1.z=table2.z) WHERE z IN (SELECT z FROM table2) 不过这个语句的性能不太好。可以用临时表解决这个问题: SELECT z,min(y) AS m INTO #tmp FROM table2 group by z UPDATE table1 set x=b.m FROM table1 a,#tmp b WHERE a.z=b.z
where table1.z=table2.z;
where table1.z=table2.z group by table2.z;
聚合不应出现在 UPDATE 语句的集合列表中。
为什么没人愿意用临时表呢?
用临时表这个问题就非常简单了。实际上SQL SERVER本身在执行SQL语句时也经常用临时表的。
UPDATE table1 SET x = t1.a from
(SELECT z,min(y) as a from table2 where table1.z=table2.z group by z) t1
where table1.z=t1.z
UPDATE table1 SET x = t1.a from
(SELECT table1.z,min(table2.y) as a from table1,table2 where table1.z=table2.z group by table1.z) t1
where table1.z=t1.z
需要注意的是如果table1中有不满足table1.z=table2.z的记录,那么这些记录中的x字段的值将被置为NULL。如果你不想更新这些记录,可以这样写:
UPDATE table1 SET x =
(SELECT min(y) from table2 where table1.z=table2.z)
WHERE z in (select z from table2)
不过这个语句的性能不太好。可以用临时表解决这个问题:
select z,min(y) as m into #tmp from table2
update table1 set x=b.m from table1 a,#tmp1 b
where a.z=b.z
需要注意的是如果table1中有不满足table1.z=table2.z的记录,那么这些记录中的x字段的值将被置为NULL。如果你不想更新这些记录,可以这样写:
UPDATE table1 SET x =
(SELECT min(y) FROM table2 WHERE table1.z=table2.z)
WHERE z IN (SELECT z FROM table2)
不过这个语句的性能不太好。可以用临时表解决这个问题:
SELECT z,min(y) AS m INTO #tmp FROM table2
UPDATE table1 set x=b.m FROM table1 a,#tmp b
WHERE a.z=b.z
你的语句没错,提高性能的关键是对table2中的z字段作索引。
需要注意的是如果table1中有不满足table1.z=table2.z的记录,那么这些记录中的x字段的值将被置为NULL。如果你不想更新这些记录,可以这样写:
UPDATE table1 SET x =
(SELECT min(y) FROM table2 WHERE table1.z=table2.z)
WHERE z IN (SELECT z FROM table2)
不过这个语句的性能不太好。可以用临时表解决这个问题:
SELECT z,min(y) AS m INTO #tmp FROM table2 group by z
UPDATE table1 set x=b.m FROM table1 a,#tmp b
WHERE a.z=b.z