INSERT ... ON DUPLICATE KEY UPDATE Syntax
카테고리 없음 / 2011. 10. 19. 12:48
SQL Server 에서 지원하는 방법은 다음과 같은 Merge 구문이 가능하다.
그 외 join update 에 대해서도 간단하게 설명한다.
테이블 생성 - not null 컬럼을 테스트 하기 위해 원 아티클에서 컬럼 하나를 더 추가함
Merge 구문 에서 not null 컬럼을 처리하기 위한 방법
INSERT ... ON DUPLICATE KEY UPDATE Syntax 는 MSSQL 에서 위와 같이 바꿀 수 있다.
아래는 join update 의 설명이다.
sql server 에서는 오래전부터 update from 절을 제공해 오고 있고, 이것은 같은행을 무작위 update 할 수 있으며, merge 구문은 그런 현상이 발생하면 에러가 발생하게 되어 좀 더 안정적이다. udpate from 절은 ansi-sql 에서 없는 구문이며, sql server 만 지원하고 있다.
sql server 의 update from 예
그 외 join update 에 대해서도 간단하게 설명한다.
테이블 생성 - not null 컬럼을 테스트 하기 위해 원 아티클에서 컬럼 하나를 더 추가함
--Create a target table
drop table Products
drop table UpdatedProducts
go
CREATE TABLE Products
(
ProductID INT PRIMARY KEY,
ProductName VARCHAR(100),
Rate MONEY,
notnullcol int not null
)
GO
--Insert records into target table
INSERT INTO Products
VALUES
(1, 'Tea', 10.00,1),
(2, 'Coffee', 20.00,1),
(3, 'Muffin', 30.00,1),
(4, 'Biscuit', 40.00,1)
GO
--Create source table
CREATE TABLE UpdatedProducts
(
ProductID INT PRIMARY KEY,
ProductName VARCHAR(100),
Rate MONEY
)
GO
--Insert records into source table
INSERT INTO UpdatedProducts
VALUES
(1, 'Tea', 10.00),
(2, 'Coffee', 25.00),
(3, 'Muffin', 35.00),
(5, 'Pizza', 60.00)
GO
SELECT * FROM Products
SELECT * FROM UpdatedProducts
GO
drop table Products
drop table UpdatedProducts
go
CREATE TABLE Products
(
ProductID INT PRIMARY KEY,
ProductName VARCHAR(100),
Rate MONEY,
notnullcol int not null
)
GO
--Insert records into target table
INSERT INTO Products
VALUES
(1, 'Tea', 10.00,1),
(2, 'Coffee', 20.00,1),
(3, 'Muffin', 30.00,1),
(4, 'Biscuit', 40.00,1)
GO
--Create source table
CREATE TABLE UpdatedProducts
(
ProductID INT PRIMARY KEY,
ProductName VARCHAR(100),
Rate MONEY
)
GO
--Insert records into source table
INSERT INTO UpdatedProducts
VALUES
(1, 'Tea', 10.00),
(2, 'Coffee', 25.00),
(3, 'Muffin', 35.00),
(5, 'Pizza', 60.00)
GO
SELECT * FROM Products
SELECT * FROM UpdatedProducts
GO
Merge 구문 에서 not null 컬럼을 처리하기 위한 방법
MERGE Products AS TARGET
--USING UpdatedProducts AS SOURCE
USING
(select 1 ProductID, 'Tea' ProductName, 10.00 Rate union all
select 2 , 'Tea' , 10.00 union all
select 3 , 'Tea' , 10.00 union all
select 5 , 'Tea' , 10.00 ) AS SOURCE
ON (TARGET.ProductID = SOURCE.ProductID)
--When records are matched, update
--the records if there is any change
WHEN MATCHED AND TARGET.ProductName <> SOURCE.ProductName
OR TARGET.Rate <> SOURCE.Rate THEN
UPDATE SET TARGET.ProductName = SOURCE.ProductName,
TARGET.Rate = SOURCE.Rate
--When no records are matched, insert
--the incoming records from source
--table to target table
WHEN NOT MATCHED BY TARGET THEN
INSERT (ProductID, ProductName, Rate,notnullcol )
VALUES (SOURCE.ProductID, SOURCE.ProductName, SOURCE.Rate, 0)
--When there is a row that exists in target table and
--same record does not exist in source table
--then delete this record from target table
WHEN NOT MATCHED BY SOURCE THEN
DELETE
--$action specifies a column of type nvarchar(10)
--in the OUTPUT clause that returns one of three
--values for each row: 'INSERT', 'UPDATE', or 'DELETE',
--according to the action that was performed on that row
OUTPUT $action,
DELETED.ProductID AS TargetProductID,
DELETED.ProductName AS TargetProductName,
DELETED.Rate AS TargetRate,
INSERTED.ProductID AS SourceProductID,
INSERTED.ProductName AS SourceProductName,
INSERTED.Rate AS SourceRate;
SELECT @@ROWCOUNT;
GO
SELECT * FROM Products
SELECT * FROM UpdatedProducts
--USING UpdatedProducts AS SOURCE
USING
(select 1 ProductID, 'Tea' ProductName, 10.00 Rate union all
select 2 , 'Tea' , 10.00 union all
select 3 , 'Tea' , 10.00 union all
select 5 , 'Tea' , 10.00 ) AS SOURCE
ON (TARGET.ProductID = SOURCE.ProductID)
--When records are matched, update
--the records if there is any change
WHEN MATCHED AND TARGET.ProductName <> SOURCE.ProductName
OR TARGET.Rate <> SOURCE.Rate THEN
UPDATE SET TARGET.ProductName = SOURCE.ProductName,
TARGET.Rate = SOURCE.Rate
--When no records are matched, insert
--the incoming records from source
--table to target table
WHEN NOT MATCHED BY TARGET THEN
INSERT (ProductID, ProductName, Rate,notnullcol )
VALUES (SOURCE.ProductID, SOURCE.ProductName, SOURCE.Rate, 0)
--When there is a row that exists in target table and
--same record does not exist in source table
--then delete this record from target table
WHEN NOT MATCHED BY SOURCE THEN
DELETE
--$action specifies a column of type nvarchar(10)
--in the OUTPUT clause that returns one of three
--values for each row: 'INSERT', 'UPDATE', or 'DELETE',
--according to the action that was performed on that row
OUTPUT $action,
DELETED.ProductID AS TargetProductID,
DELETED.ProductName AS TargetProductName,
DELETED.Rate AS TargetRate,
INSERTED.ProductID AS SourceProductID,
INSERTED.ProductName AS SourceProductName,
INSERTED.Rate AS SourceRate;
SELECT @@ROWCOUNT;
GO
SELECT * FROM Products
SELECT * FROM UpdatedProducts
INSERT ... ON DUPLICATE KEY UPDATE Syntax 는 MSSQL 에서 위와 같이 바꿀 수 있다.
아래는 join update 의 설명이다.
sql server 에서는 오래전부터 update from 절을 제공해 오고 있고, 이것은 같은행을 무작위 update 할 수 있으며, merge 구문은 그런 현상이 발생하면 에러가 발생하게 되어 좀 더 안정적이다. udpate from 절은 ansi-sql 에서 없는 구문이며, sql server 만 지원하고 있다.
sql server 의 update from 예
UPDATE c
SET Street = m.Street,
HouseNo = m.HouseNo,
City = m.City
FROM Customers AS c
INNER JOIN Moved AS m
ON m.SSN = c.SSN;
mysql 의 경우 다음과 같은 구문을 제공한다.
UPDATE (
SELECT site_code, company_code, description
FROM site_info
WHERE site_code > 2645
AND site_code NOT IN (2721,2720,2719,2718,2717,2716)
ORDER BY site_code DESC
) a
JOIN site_threshold_set b
ON a.site_code = b.site_code
AND a.company_code = b.company_code
JOIN (
SELECT *
FROM site_threshold_set
WHERE site_code = 2710
) c
ON b.alarm_code = c.alarm_code
SET b.critical = c.critical
, b.warning = c.warning
, b.notice = c.notice
oracle 의 경우 subquery 와 merge 를 이용한 join update 가 가능하다.
A Simple Guide to Five Normal Forms in Relational Database .pdf
정규화 adx.ppt
soft_numa_example.reg
sqltag org online seminar 01.zip
