Update 的一个例子

在CU上看到有人介绍可以用common table expression来实现update，感觉思路很好，很容易理解。但有人说不能成功。实践了一下，还真不行。不过感觉对于复杂的update，可以借用这个思路，用一个真正的表（或者临时表）替换common table expression就好了。

    下面假设有这样一个场景：t1, t2 ,t3相互关联，t4实际上是这三个表组合查询出来的一个结果集，然后第二字段被人为进行了调整，最后根据这个调整反过来update表t3。
drop table t1;
drop table t2;
drop table t3;
create table t1 (c1 int, c2 varchar(2));
create table t2 (c1 int, c2 varchar(2));
create table t3 (c1 int, c2 int, c3 varchar(2));
insert into t1 values (1,'a'),(2,'b'),(3,'c');
insert into t2 values (11,'aa'),(22,'bb'),(33,'cc');
insert into t3 values (1,11,'x'),(2,22,'y'),(3,33,'z');

drop table t4;
create table t4 (c1 varchar(2), c2 varchar(2));
insert into t4 values ('b','aa'),('c','bb'),('a','cc');

    简单的说，t3原本如此：
select * from t3
C1          C2          C3
----------- ----------- --
          1          11 x
          2          22 y
          3          33 z
  3 条记录已选择。

    根据t4的内容最终需要调整成这样：
select * from t3
C1          C2          C3
----------- ----------- --
          1          33 x
          2          11 y
          3          22 z
  3 条记录已选择。

    用common table expression会报错
with c as
(select a.c1, t2.c1 as c6
from
(select t3.*, t1.c2 as c5, t4.c2 as c4
from t1, t3, t4
where t1.c1=t3.c1 and t1.c2=t4.c1) as a
left join t2
on a.c4=t2.c2)
update t3 set c2=(select c6 from c where t3.c1 = c.c1)
where exists (select c6 from c where t3.c1 = c.c1);

    但如果是真正的表就能成功
drop table c;
create table c (c1 int, c6 int);
insert into c
select a.c1, t2.c1 as c6
from
(select t3.*, t1.c2 as c5, t4.c2 as c4
from t1, t3, t4
where t1.c1=t3.c1 and t1.c2=t4.c1) as a
left join t2
on a.c4=t2.c2;
update t3 set c2=(select c6 from c where t3.c1 = c.c1)
where exists (select c6 from c where t3.c1 = c.c1);

    不知各位有何见解？

[[i] 本帖最后由 beginner-bj 于 2007-3-13 10:29 编辑 [/i]]