데이터베이스 질문과 답변
Id|Name |EmployeeNumber|DepartmentName|Detail|Mac|Card |CardCrypt|Flag|
--+--------+--------------+--------------+------+---+----------+---------+----+
|chocho |1234 |dep1 |d1 | |1234567890| | |
|yoonyoon |2 |dep2 |d2 | |3500078324| | |
이런 테이블 입니다.
다른 테이블 조인 해서
데이터를 가져와서 해당 테이블에 merge into 하고 싶습니다.
카드값을 기준을 기준으로 해당 카드가 있으면 다른 컬럼 update 없으면 insert 하고 싶습니다.
다른 테이블에서 조인해온 데이터에 카드값이 항상있습니다.
제가 삽입하고 싶은 테이블에도 카드값은 항상있습니다.
원본 테이블 이랑 삽입하는 컬럼이랑 데이터가 같아도 항상 update row가 삽입하는 데이터 만큼 뜹니다.
원본이랑 같으면 무시해야 될것 같아서요....
MERGE INTO VAIUsersUI AS vu
USING (
SELECT COALESCE(dp.PersonLastName , N'') + COALESCE(dp.PersonFirstName, N'') AS Name ,dp.PersonID as EmployeeNumber,dp.DepartmentName, dp.PersonMemo as Detail ,dpc.IDRF as Card
FROM Data_Person dp
LEFT OUTER JOIN Data_Person_Cardholder dpc ON dp.PersonID = dpc.PersonID
AND dpc.IDRFUSE = 1) AS b
ON (vu.Card = b.Card)
WHEN MATCHED THEN
UPDATE SET vu.Name = b.Name ,vu.EmployeeNumber = b.EmployeeNumber ,vu.DepartmentName = b.DepartmentName ,vu.Detail = b.Detail,vu.Flag=1
WHEN NOT MATCHED THEN
INSERT(Name, EmployeeNumber, DepartmentName,Detail,Card,Flag) VALUES(b.Name, b.EmployeeNumber, b.DepartmentName,b.Detail,b.Card,0)
;
Flag 가 3개있습니다. 해당 플래그를 merge into에서 셋팅할수 있을까요?
| 플래그 | card | card enc | 설명 |
| 0 | 존재 | 없음 | 외부 에서 card enc 생성 필요 insert |
| 1 | 존재 | 존재 | 잘맞음 최종 상태 |
| 2 | 존재 | 존재 | 외부 에 name 등 다른 데이터 update |
현재 상황
테이블이 2개 있고 테이블의 CardCrypt를 외부에서 생성 해야합니다.
데이터를 이렇게 가지고 있습니다.
테이블1: 이름 사원번호 카드
테이블2: 이름 사원번호 카드 카드enc
enc 생성 서버: 이름 사원번호 카드 카드enc
테이블 2에 테이블1 데이터 삽입
테이블 2에 이름 사원번호 카드 enc 생성 서버에 삽입 카드enc 생성
끝------------
새롭게
테이블 1 수정 발생
테이블 2 에서 update, insert Flag 설정 가능?
발급 서버에서 flag 0은 생성
발급 서버에서 flag 3은 수정 이렇게 하려고 합니다
머지는 키컬럼이 같은게 존재하면 업데이트 없으면 인서트 하는 구문인데요?
질문의 내용이 무슨 말인지 잘 이해가 안가네요?
예를 들어
대상(merge into) 테이블에 카드가(1,2,3) 이 존재하고
참조(using) 테이블에 카드가(2,3,4) 가 존재한다면?
(2,3,4)를 기준으로 (2,3) 은 Update, (4) 는 Insert 입니다.
아~ 아예 merge 자체를 잘못 생각했네요.
전 3가지 상태인줄....
완전이 같으면 생략
일부가 다르면 update
없으면 insert
질문 추가 했는데 가능하시면 한번 읽어주세요
추가 질문도 잘 이해가 안가네요.
정리가 필요합니다.
UPDATE VAIUsersUI
SET VAIUsersUI.Name = vt.Name,
VAIUsersUI.EmployeeNumber = vt.EmployeeNumber,
VAIUsersUI.Flag = vt.Flag
FROM (
SELECT vu.Id,vt.Name, vt.EmployeeNumber,vt.DepartmentName,vt.Detail, 3 as Flag FROM VAIUsersUI vu
LEFT JOIN VAITT vt ON vu.Card = vt.Card
WHERE vu.Name <> vt.Name
OR vu.EmployeeNumber <> vt.EmployeeNumber
OR vu.DepartmentName <> vt.DepartmentName
OR vu.Detail <> vt.Detail
) as vt
WHERE
vt.Id = VAIUsersUI.Id;
답변 감사드립니다.
또다른 질문인데.....
select join 한 결과를 update 하는 퀴리입니다.
되긴 되는데 뭔가 비효율적으로 짠것 같아서요
VAIUsersUI와 VAITT를 비교해서 특정 컬럼의 데이터가 서로 다르면 VAIUsersUI의 FLAG를 3으로 해주는 쿼리입니다.
UPDATE VAIUsersUI vu
SET vu.Name = vt.Name
, vu.EmployeeNumber = vt.EmployeeNumber
, vu.DepartmentName = vt.DepartmentName
, vu.Detail = vt.Detail
, vu.Flag = 3
FROM VAITT vt
WHERE vu.Card = vt.Card
AND ( vu.Name <> vt.Name
OR vu.EmployeeNumber <> vt.EmployeeNumber
OR vu.DepartmentName <> vt.DepartmentName
OR vu.Detail <> vt.Detail
)
;
답변감사합니다.
예쁘게 정리 됐네요
From 안에 서브 퀘리를 join으로 바꿔 주셨는데
어떻게 찾아보면 될까요?
쿼리를 짜다보니 From 안에 서브 퀘리를 많이 쓰게 되는데 보기도 안좋고 성능상 별로 라고....
찾아보니 where 안의 서브 퀴리를 join으로 바꾸는 글은 보입니다.
From 안에 서브 퀘리를 join으로 바꾸는건 못찾아서요.
VAITT가 테스트 테이블 이었고
FROM안에 join이 들어간 서브퀴리를 넣어서 잘 작동합니다.
정리해주셨던것 처럼 From 안에 서브쿼리를 join으로 바꿔보고 싶어서요.
//업데이트로 flag 설정
UPDATE VAIUsersUI
SET Name = vt.Name
, EmployeeNumber = vt.EmployeeNumber
, DepartmentName = vt.DepartmentName
, Flag = IIF(CardCrypt IS NULL, 0, 2)
FROM (SELECT
-- COALESCE(dp.PersonLastName , N'') + COALESCE(dp.PersonFirstName, N'') AS Name ,dp.PersonID,dp.DepartmentName, dp.PersonMemo AS Detail ,dpc.IDRF AS Card
CONCAT(dp.PersonLastName,dp.PersonFirstName) AS Name ,dp.PersonID AS EmployeeNumber,dp.DepartmentName, dpc.IDRF AS Card
FROM
Data_Person dp
LEFT OUTER JOIN Data_Person_Cardholder dpc ON
dp.PersonID = dpc.PersonID
AND dpc.IDRFUSE = 1) vt
WHERE VAIUsersUI.Card = vt.Card
--AND VAIUsersUI.CardCrypt IS NOT NULL
AND ( VAIUsersUI.Name <> vt.Name
OR VAIUsersUI.EmployeeNumber <> vt.EmployeeNumber
OR VAIUsersUI.DepartmentName <> vt.DepartmentName
)
;
// 인서트 조건 카드값으로
INSERT
INTO
VAIUsersUI (Name,EmployeeNumber,DepartmentName,Card)
SELECT
vt.Name,vt.EmployeeNumber,vt.DepartmentName,vt.Card
FROM (SELECT
-- COALESCE(dp.PersonLastName , N'') + COALESCE(dp.PersonFirstName, N'') AS Name ,dp.PersonID,dp.DepartmentName, dp.PersonMemo AS Detail ,dpc.IDRF AS Card
CONCAT(dp.PersonLastName,dp.PersonFirstName) AS Name ,dp.PersonID AS EmployeeNumber,dp.DepartmentName, dpc.IDRF AS Card
FROM
Data_Person dp
LEFT OUTER JOIN Data_Person_Cardholder dpc ON
dp.PersonID = dpc.PersonID
AND dpc.IDRFUSE = 1) vt
WHERE
NOT EXISTS
(
SELECT Card
FROM
VAIUsersUI vu
WHERE
vu.Card = vt.Card
)
;
UPDATE VAIUsersUI vu
SET Name = CONCAT(dp.PersonLastName, dp.PersonFirstName)
, EmployeeNumber = dp.PersonID
, DepartmentName = dp.DepartmentName
, Flag = IIF(CardCrypt IS NULL, 0, 2)
FROM Data_Person dp
INNER JOIN Data_Person_Cardholder dpc
ON dp.PersonID = dpc.PersonID
AND dpc.IDRFUSE = 1
WHERE vu.Card = dpc.IDRF
AND ( vu.Name <> CONCAT(dp.PersonLastName, dp.PersonFirstName)
OR vu.EmployeeNumber <> dp.PersonID
OR vu.DepartmentName <> dp.DepartmentName
)
;
INSERT INTO VAIUsersUI (Name, EmployeeNumber, DepartmentName, Card)
SELECT CONCAT(dp.PersonLastName,dp.PersonFirstName) AS Name
, dp.PersonID AS EmployeeNumber
, dp.DepartmentName
, dpc.IDRF AS Card
FROM Data_Person dp
INNER JOIN Data_Person_Cardholder dpc
ON dp.PersonID = dpc.PersonID
AND dpc.IDRFUSE = 1
LEFT OUTER JOIN VAIUsersUI vu
ON dpc.IDRF = vu.Card
WHERE vu.Card IS NULL
;
MERGE INTO VAIUsersUI vu
USING
(
SELECT CONCAT(dp.PersonLastName,dp.PersonFirstName) AS Name
, dp.PersonID AS EmployeeNumber
, dp.DepartmentName
, dpc.IDRF AS Card
FROM Data_Person dp
INNER JOIN Data_Person_Cardholder dpc
ON dp.PersonID = dpc.PersonID
AND dpc.IDRFUSE = 1
LEFT OUTER JOIN VAIUsersUI vu
ON dpc.IDRF = vu.Card
WHERE vu.Card IS NULL
OR vu.Name <> CONCAT(dp.PersonLastName, dp.PersonFirstName)
OR vu.EmployeeNumber <> dp.PersonID
OR vu.DepartmentName <> dp.DepartmentName
) vt
ON (vu.Card = vt.Card)
WHEN MATCHED THEN
UPDATE
SET vu.Name = vt.Name
, vu.EmployeeNumber = vt.EmployeeNumber
, vu.DepartmentName = vt.DepartmentName
, vu.Flag = CASE WHEN vu.CardCrypt IS NULL THEN 0 ELSE 2 END
WHEN NOT MATCHED THEN
INSERT ( Name, EmployeeNumber, DepartmentName, Card, Flag)
VALUES (vt.Name, vt.EmployeeNumber, vt.DepartmentName, vt.Card, 0)
;
) 버튼을 클릭하여 작성 하시면 됩니다.