August 10, 2014 at 8:14 pm
Table A - data example
Idno Entity Flag
1 1 y
2 1 n
3 1 y
4 1 y
Table B - data example
Idno Entity
1 1
2 1
3 1
Table C - data example
Idno Entity
4 1
I want to update the value flag in table A.
Update flag from n to y if idno A in table B and and idno A not in table C, so from table A above, first record and 3rd record remain unchange but 2nd record, flag will be changed from n to y.
4th record where idno A not in table b and in Table C so flag will change from y to n.
Can i know how to do the SQl query on this? is it possible, or do i need to use the "fetch" method to compare record by record. please advise
August 10, 2014 at 11:16 pm
There are quite few ways of doing this, here is one that uses left outer joins and a case statement:
😎
USE tempdb;
GO
/*
Table A - data example
Idno Entity Flag
1 1 y
2 1 n
3 1 y
4 1 y
*/
DECLARE @TA TABLE
(
Idno INT
,Entity INT
,Flag CHAR(1)
);
INSERT INTO @TA(Idno,Entity,Flag)
VALUES
(1, 1,'y')
,(2, 1,'n')
,(3, 1,'y')
,(4, 1,'y');
/*
Table B - data example
Idno Entity
1 1
2 1
3 1
*/
DECLARE @TB TABLE
(
Idno INT
,Entity INT
);
INSERT INTO @TB (Idno,Entity)
VALUES
(1, 1)
,(2, 1)
,(3, 1);
/*
Table C - data example
Idno Entity
4 1
*/
DECLARE @TC TABLE
(
Idno INT
,Entity INT
);
INSERT INTO @TC(Idno,Entity)
VALUES (4,1);
;WITH SRC_BASE AS
(
SELECT
A.Idno
,A.Entity
,CASE
WHEN B.Idno IS NOT NULL THEN 'y'
WHEN C.Idno IS NOT NULL THEN 'n'
ELSE NULL
END AS Flag
FROM @TA A
LEFT OUTER JOIN @TB B
ON A.Idno = B.Idno
AND A.Entity = B.Entity
LEFT OUTER JOIN @TC C
ON A.Idno = C.Idno
AND A.Entity = C.Entity
)
UPDATE A
SET A.Flag = SB.Flag
FROM SRC_BASE SB
INNER JOIN @TA A
ON SB.Idno = A.Idno
AND SB.Entity = A.Entity
WHERE A.Flag <> SB.Flag;
SELECT * FROM @TA
Results
Idno Entity Flag
----------- ----------- ----
1 1 y
2 1 y
3 1 y
4 1 n
August 11, 2014 at 12:28 am
thanks bro, it's work like a charm!
Assume now my current date in table A, entity field is char
table data like below , but data in B & C remain unchange.
Idno Entity Flag
1 a y
2 a n
3 a y
4 a y
e.g 1 = a, 2 = b, 3 = c,
in your query
LEFT OUTER JOIN @TB B
ON A.Idno = B.Idno
AND A.Entity = B.Entity
LEFT OUTER JOIN @TC C
ON A.Idno = C.Idno
AND A.Entity = C.Entity
i need to change the a to 1 first before compare(ON A.Idno = B.Idno)
how to do that?
August 11, 2014 at 12:59 am
thiensyh (8/11/2014)
thanks bro, it's work like a charm!Assume now my current date in table A, entity field is char
table data like below , but data in B & C remain unchange.
Idno Entity Flag
1 a y
2 a n
3 a y
4 a y
e.g 1 = a, 2 = b, 3 = c,
in your query
LEFT OUTER JOIN @TB BON A.Idno = B.Idno
AND A.Entity = B.Entity
LEFT OUTER JOIN @TC C
ON A.Idno = C.Idno
AND A.Entity = C.Entity
i need to change the a to 1 first before compare(ON A.Idno = B.Idno)
how to do that?
In the previous code sample I used the combined key of Idno and Entity. If Idno is the key, you can skip the Entity part:
😎
LEFT OUTER JOIN @TB B
ON A.Idno = B.Idno
LEFT OUTER JOIN @TC C
ON A.Idno = C.Idno
If there is a mapping between the two values it can be done with a simple case statement, here is a quick conversion from the previous sample.
USE tempdb;
GO
/*
Table A - data example
Idno Entity Flag
1 1 y
2 1 n
3 1 y
4 1 y
*/
DECLARE @TA TABLE
(
Idno INT
,Entity CHAR(1)
,Flag CHAR(1)
);
INSERT INTO @TA(Idno,Entity,Flag)
VALUES
(1, 'a','y')
,(2, 'a','n')
,(3, 'a','y')
,(4, 'a','y');
/*
Table B - data example
Idno Entity
1 1
2 1
3 1
*/
DECLARE @TB TABLE
(
Idno INT
,Entity INT
);
INSERT INTO @TB (Idno,Entity)
VALUES
(1, 1)
,(2, 1)
,(3, 1);
/*
Table C - data example
Idno Entity
4 1
*/
DECLARE @TC TABLE
(
Idno INT
,Entity INT
);
INSERT INTO @TC(Idno,Entity)
VALUES (4,1);
;WITH SRC_BASE AS
(
SELECT
A.Idno
,A.Entity
,CASE
WHEN A.Entity = 'a' THEN 1
WHEN A.Entity = 'b' THEN 2
WHEN A.Entity = 'c' THEN 3
ELSE NULL
END AS EntityChar
,CASE
WHEN B.Idno IS NOT NULL THEN 'y'
WHEN C.Idno IS NOT NULL THEN 'n'
ELSE NULL
END AS Flag
FROM @TA A
LEFT OUTER JOIN @TB B
ON A.Idno = B.Idno
AND B.Entity = CASE
WHEN A.Entity = 'a' THEN 1
WHEN A.Entity = 'b' THEN 2
WHEN A.Entity = 'c' THEN 3
ELSE NULL
END
LEFT OUTER JOIN @TC C
ON A.Idno = C.Idno
AND C.Entity = CASE
WHEN A.Entity = 'a' THEN 1
WHEN A.Entity = 'b' THEN 2
WHEN A.Entity = 'c' THEN 3
ELSE NULL
END
)
UPDATE A
SET A.Flag = SB.Flag
FROM SRC_BASE SB
INNER JOIN @TA A
ON SB.Idno = A.Idno
AND SB.Entity = A.Entity
WHERE A.Flag <> SB.Flag;
SELECT * FROM @TA
August 11, 2014 at 1:41 am
thanks a lot!
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply