--1. 일반적인 방법
UPDATE [TABLE_1]
SET COL1 = SUB_COL1
, COL2 = SUB_COL2
, COL3 = SUB_COL3
FROM
(
SELECT SUB_COL1
, SUB_COL2
, SUB_COL3
, USER_NUM
FROM [TABLE_2]
WHERE [조건...]
) SUB1
WHERE [TABLE_1].USER_NUM = SUB1.USER_NUM
--2. CTE 를 사용한 방법
WITH CTE_TABLE AS
(
SELECT SUB_COL1, SUB_COL2, SUB_COL3, USER_NUM
FROM [TABLE_2]
WHERE [조건...]
)
UPDATE [TABLE_1]
SET COL1 = SUB_COL1
, COL2 = SUB_COL2
, COL3 = SUB_COL3
FROM CTE_TABLE SUB1
WHERE [TABLE_1].USER_NUM = SUB1.USER_NUM
-- 1. 먼저 현재값과 update해야할 값을 조회해본다.
select a.컬럼1, b.컬럼1
,a.컬럼2, b.컬럼2
,a.컬럼3, b.컬럼3
,a.컬럼4, b.컬럼4
FROM 테이블1 a inner join 테이블2 b on a.컬럼1 =b.컬럼2 and a.컬럼1_1=b.컬럼2_2
where a.컬럼 = 조건
-- 2. ,을 =로 바꾸주고 바로 update 해준다.
update a set a.컬럼1 = b.컬럼1
,a.컬럼2 = b.컬럼2
,a.컬럼3 = b.컬럼3
,a.컬럼4 = b.컬럼4
FROM 테이블1 a inner join 테이블2 b on a.컬럼1 =b.컬럼2 and a.컬럼1_1=b.컬럼2_2
where a.컬럼 = 조건
IF(<condition is true>)
BEGIN
<execute some code>
END
ELSE IF(<different condition is true>)
BEGIN
<execute some other code>
END
ELSE
BEGIN
<execute some other other code>
END
SELECT empno
, ename
, job
, sal
, LAG(sal) OVER(PARTITION BY job ORDER BY job, sal) AS sal_prev
, LEAD(sal) OVER(PARTITION BY job ORDER BY job, sal) AS sal_next
FROM emp
WHERE job IN ('MANAGER', 'ANALYST', 'SALESMAN')
ORDER BY job, sal
CREATE TABLE T (a INT, b INT, c INT);
GO
INSERT INTO T VALUES (1, 1, -3), (2, 2, 4), (3, 1, NULL), (4, 3, 1), (5, 2, NULL), (6, 1, 5);
SELECT b, c,
LAG(2*c, b*(SELECT MIN(b) FROM T), -c/2.0) OVER (ORDER BY a) AS i
FROM T;
CREATE TABLE T (a INT, b INT, c INT);
GO
INSERT INTO T VALUES (1, 1, -3), (2, 2, 4), (3, 1, NULL), (4, 3, 1), (5, 2, NULL), (6, 1, 5);
SELECT b, c,
LEAD(2*c, b*(SELECT MIN(b) FROM T), -c/2.0) OVER (ORDER BY a) AS i
FROM T;
USE AdventureWorks2012;
GO
-- Create tool table.
CREATE TABLE dbo.Tool(
ID INT IDENTITY NOT NULL PRIMARY KEY,
Name VARCHAR(40) NOT NULL
);
GO
-- Inserting values into products table.
INSERT INTO dbo.Tool(Name)
VALUES ('Screwdriver')
, ('Hammer')
, ('Saw')
, ('Shovel');
GO
-- Create a gap in the identity values.
DELETE dbo.Tool
WHERE Name = 'Saw';
GO
SELECT *
FROM dbo.Tool;
GO
-- Try to insert an explicit ID value of 3;
-- should return an error:
-- An explicit value for the identity column in table 'AdventureWorks2012.dbo.Tool' can only be specified when a column list is used and IDENTITY_INSERT is ON.
INSERT INTO dbo.Tool (ID, Name) VALUES (3, 'Garden shovel');
GO
-- SET IDENTITY_INSERT to ON.
SET IDENTITY_INSERT dbo.Tool ON;
GO
-- Try to insert an explicit ID value of 3.
INSERT INTO dbo.Tool (ID, Name) VALUES (3, 'Garden shovel');
GO
SELECT *
FROM dbo.Tool;
GO
-- Drop products table.
DROP TABLE dbo.Tool;
GO