SQL 收藏

alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss'

 

update jiajia_child_t set parent_family_id=decode(parent_family_id,101,55,parent_family_id)
,child_family_id=decode(child_family_id,101,55,child_family_id)
,is_real=(CASE when parent_family_id=101  THEN 0 when child_family_id=101 then 0  ELSE is_real end)WHERE id_jiajia=41

 

 

select * FROM jiajia_t WHERE (id_jiajia='' and '' is not null or  '' is NULL )
and (name_jiajia like '%h%' and 'h' is not null or 'h' is NULL)
and (family_id = '' and '' is not null or '' is null)

 

 

begin
for x in (select * from user_objects where object_name like'%/_T' escape '/'and object_type='TABLE' ) loop
EXECUTE immediate 'drop table '||x.object_name||' cascade constraints';
end loop;
commit;
end;

 

PURGE recyclebin;

 

===========================

sql Server 的多表更新：

 

update local_songs set local_copyright_no=t2.copyright_no
from local_songs t1,copyright_songs t2
where (t1.name=t2.name and t1.singer=t2.singer)

Oracle 的多表更新：

update ( select t1.id,t1.local_copyright_no,t2.copyright_no
from local_songs t1,local_songs_tmp t2
         where t1.id=t2.id
       ) tt
   set tt.local_copyright_no=tt.copyright_no;
  
commit;

===========================