update table1 t1 set t1.code=t2.code from table2 t2,table1 t1 where t2.t1_id=t1.id and t1.name='aa'就是想从table2表中取code更新到对应的table1中,(table2中有个t1_id字段和t1的id是关联的) 应该怎么写才行?谢谢,用的order
update table1 t1 set t1.code = (select t2.code from table2 t2 where t2.t1_id = t1.id and t1.name = 'aa')
oracle有些版本不这种写法,据说oracle11g、12g支持,但是我没有试过。
update table1 t1 set t1.code=t2.code from table2 t2,table1 t1 where t2.t1_id=t1.id and t1.name='aa' 改为update datable1 t1 set (select t2.code from table2 t2 where t2.tl_id=t1.id) where t1.name='aa'
update datable1 t1 set t1.code=(select t2.code from table2 t2 where t2.tl_id=t1.id and t1.name='aa' ) where exists (select 1 from table2 t2 where t2.tl_id=t1.id) and t1.name='aa'
and t1.name = 'aa')
oracle有些版本不这种写法,据说oracle11g、12g支持,但是我没有试过。
改为update datable1 t1
set (select t2.code from table2 t2 where t2.tl_id=t1.id)
where t1.name='aa'
set t1.code=(select t2.code from table2 t2 where t2.tl_id=t1.id and t1.name='aa' )
where exists (select 1 from table2 t2 where t2.tl_id=t1.id)
and t1.name='aa'
你这样写,不是多此一举吗?后面需要用到exists吗?