[MSSQL]권한 및 테이블 복사, 업데이트(Update) Query

--특정필드 일괄 업데이트 쿼리

--Oracle

update bf_import_req a set

       (receipt_date) =

       (select receipt_date from (select bl_no, max(receipt_date) receipt_date from SGINV_ECIS_INF_RECEIVE_V group by bl_no) b where b.bl_no = a.bl_no)

where exists (select 'X' from SGINV_ECIS_INF_RECEIVE_V b where b.bl_no = a.bl_no)

 

update stmst_old a set

       (sdno, topname) =

       (select sdno, topname from stmst b where b.stcode = a.stcode)

where exists (select 'X' from stmst b where b.stcode = a.stcode)

-- exists하는 이유는 select 결과가 null인경우는 앞의 필드가 모두 null로 되기 때문에.... 

 

 

--Ms Sql Server

update stmst_old set

       sdno = (select sdno from stmst where stmst_old.stcode = stmst.stcode),

       topname = (select topname from stmst where stmst_old.stcode = stmst.stcode)

where exists (select 'X' from stmst where stmst_old.stcode = stmst.stcode)

 

 

update stmst_trans set

       bwh_name = (select bwh_name from uTms.dbo.coLoc where stmst_trans.bwh_id = uTms.dbo.coLoc.bwh_id)

     , bwh_addr = (select bwh_addr from uTms.dbo.coLoc where stmst_trans.bwh_id = uTms.dbo.coLoc.bwh_id)

where exists (select 'X' from uTms.dbo.coLoc where stmst_trans.bwh_id = uTms.dbo.coLoc.bwh_id)

 

 

oracle 테이블 접근 권한주기

grant select, insert, update, delete on posco_payment to kwangyang

 

Table 복사하기

Oracle : Create Table NEW테이블명 as Select * from 테이블명 

Sql, Mdb : Select * Into NEW테이블명 From 테이블명 Where ...

 

테이블명 변경하기

oracle - rename pojang1 to pojang

ms-sql - sp_rename pojang1, pojang

 

connect system as sysdba

비번 : oracle

 

     CREATE USER TEST(계정명) IDENTIFIED BY TESTPW(비밀번호); 대소문자 상관없음

     계정명 : CTH 비번 : oracle

 

 - 생성된 계정을 확인

   (모든 db서버 내의 모든 계정 검색)

   SELECT * FROM DBA_USERS;

 

grant명령어로 접속, 사용 권한주기

grant connect, resource, dba to kim(계정명);