子查询的款困惑

zhaoyh603

select  (select a.dept_fullname from ad_department a where a.dept_id = parent_id)||'_'||dept_name as b ,dept_name,parent_id  from ad_department
Where Length(dept_Code) = 6    And dept_Code Like '99%' ;

b是空的

select b.dept_fullname||'_'||a.dept_name as b , a.dept_name from  ad_department a,ad_department b where a.parent_id=b.dept_id;

可以

目的是想update dept_fullname

为什么呀 疑惑

zhaoyh603

select  (select a.dept_fullname from ad_department a where a.dept_id = b.parent_id)||'_'||b.dept_name as b ,b.dept_name,b.parent_id  from ad_department b
Where Length(b.dept_Code) = 6    And b.dept_Code Like '99%' ;

加了别名就可以了，晕 why

zhaoyh603

update ad_department as b
set b.dept_fullname=(select a.dept_fullname from ad_department a where a.dept_id = b.parent_id)||'_'||b.dept_name
Where Length(b.dept_Code) = 6    And b.dept_Code Like '99%' ;

错误：
ERROR:  column "b" of relation "ad_department" does not exist
LINE 2: set b.dept_fullname=(select a.dept_fullname from ad_departme...

renxiao2003

晕呼，没看明白。

zhaoyh603

update ad_department b
set dept_fullname=a.dept_fullname||'_'||b.dept_name
from ad_department as a
Where Length(b.dept_Code) = 6    And b.dept_Code Like '99%'  and a.dept_id = b.parent_id;

这样可以

oracle这样貌似不行吧

什么标准？

zhaoyh603

看明白了吗？

trainee

语法是有差别，习惯了就好。
postgresql的语法更易理解。

5楼的语法可以这样理解：两表先join后，再更新，反之3楼的语法，更难于理解。

zhaoyh603

恩，是有差别，可是将来移植起来就麻烦啦