[MSSQL] select update 쿼리

-- MS-Sql --
MS-Sql에서 Select문을 이용한 Update 쿼리
Table_name_2 부분에 Select문을 넣어줌

UPDATE Table_name_1
SET       Table_name_1.column_name_1
                                = Table_name_2.column_name_1
FROM    Table_name_1,
            Table_name_2
WHERE  Table_name_1.column_name_pk
                                = Table_name_2.column_name_pk

- 다른테이블의 값을 쓰는 경우 - 

drop table TS_QUERY_TEST;

drop table TS_QUERY_TEST_2;

create table TS_QUERY_TEST

(

ID varchar(10) primary key,

content varchar(20)

);

create table TS_QUERY_TEST_2

(

ID varchar(10) primary key,

content varchar(20)

);

insert into TS_QUERY_TEST (id,content) values ( '1','11111');

insert into TS_QUERY_TEST (id,content) values ( '2','22222');

insert into TS_QUERY_TEST (id,content) values ( '3','33333');

insert into TS_QUERY_TEST (id,content) values ( '4','44444');

insert into TS_QUERY_TEST (id,content) values ( '5','55555');

insert into TS_QUERY_TEST_2 (id,content) values ( '1','111');

insert into TS_QUERY_TEST_2 (id,content) values ( '2','222');

insert into TS_QUERY_TEST_2 (id,content) values ( '3','333');

insert into TS_QUERY_TEST_2 (id,content) values ( '4','444');

insert into TS_QUERY_TEST_2 (id,content) values ( '5','555');

/* before */

select * from TS_QUERY_TEST;

select * from TS_QUERY_TEST_2;

update TS_QUERY_TEST

set content = (

select content from TS_QUERY_TEST_2

where TS_QUERY_TEST.ID = ID

)

/* after */

select * from TS_QUERY_TEST;

select * from TS_QUERY_TEST_2;

-동일 테이블의 값을 쓰는 경우

use shims

drop table TS_QUERY_TEST;

create table TS_QUERY_TEST

(

ID varchar(10) primary key,

content varchar(20),

pid varchar(10)

);

insert into TS_QUERY_TEST (id,content,pid) values ( '1','11111','');

insert into TS_QUERY_TEST (id,content,pid) values ( '2','22222','1');

insert into TS_QUERY_TEST (id,content,pid) values ( '3','33333','2');

insert into TS_QUERY_TEST (id,content,pid) values ( '4','44444','3');

insert into TS_QUERY_TEST (id,content,pid) values ( '5','55555','4');

/* before */

select * from TS_QUERY_TEST;

update TS_QUERY_TEST

set content = B.content

from TS_QUERY_TEST

, TS_QUERY_TEST as B

where TS_QUERY_TEST.pid = B.ID;

/* after */

select * from TS_QUERY_TEST;

[출처] http://jhoonslife.tistory.com/398