| Author |
Topic |
|
nirnir2
Starting Member
20 Posts |
 Posted - 2014-07-01 : 06:13:20
|
I have two tables
Table clients
id
------
1000
1001
Table final ( before )
id,kind,overWrite1,overWrite2,overWrite3,dontTouch1,dontTouch2
----------------------------------------------------------------------
-1 ,1,a,b,c,d,e
-1 ,2,a,b,c,nil,nil
-1 ,3,a,b,c,nil,nil
1000,1,aaa,b,c,x1,x2
1000,2,a,bbb,c,x11,x22
table final should have a record for each combination of table final (where id=-1) and clients.id
I need a sql command to update table Final ( add missing or update existing records)
fields overWrite1,overWrite2,overWrite3 should be overridden ,
fields dontTouch1,dontTouch2 shouldn't be overridden on updated records but should get the value on new records
this is how table final should look after
id,kind,overWrite1,overWrite2,overWrite3,dontTouch1,dontTouch2
-1 ,1,a,b,c,d,e
-1 ,2,a,b,c,nil,nil
-1 ,3,a,b,c,nil,nil
1000,1,a,b,c,x1,x2
1000,2,a,b,c,x11,x22
1000,3,a,b,c,nil,nil
1001,1,a,b,c,d,e
1001,2,a,b,c,nil,nil
1001,3,a,b,c,nil,nil |
|
|
stepson
Aged Yak Warrior
545 Posts |
Posted - 2014-07-01 : 07:08:57
|
[code]
;with clients
AS
(SELECT 1000 AS id Union all
SELECT 1001)
,
TFinal
AS
(
SELECT -1 AS ID,1 AS kind, 'a' AS overWrite1, 'b' AS overWrite2,'c' AS overWrite3,'d' AS dontTouch1,'e' AS dontTouch2 UNION ALL
SELECT -1 ,2,'a','b','c','nil','nil' UNION ALL
SELECT -1 ,3,'a','b','c','nil','nil' UNION ALL
SELECT 1000,1,'aaa','b','c','x1','x2' UNION ALL
SELECT 1000,2,'a','bbb','c','x11','x22')
SELECT
ISNULL(TF.id,ALLROWS.id) AS ID
,ISNULL(TF.kind,ALLROWS.kind) AS kind
,ISNULL(ALLROWS.overWrite1,TF.overWrite1) AS overWrite1
,ISNULL(ALLROWS.overWrite2,TF.overWrite2) AS overWrite2
,ISNULL(ALLROWS.overWrite3,TF.overWrite3) AS overWrite3
,ISNULL(TF.dontTouch1,ALLROWS.dontTouch1) AS dontTouch1
,ISNULL(TF.dontTouch2,ALLROWS.dontTouch2) AS dontTouch2
FROM
(
SELECT
ID,kind,overWrite1,overWrite2,overWrite3,dontTouch1,dontTouch2
FROM TFinal
) AS TF
FULL JOIN
(
SELECT
C.id
,TF.kind
,TF.overWrite1
,TF.overWrite2
,TF.overWrite3
,TF.dontTouch1
,TF.dontTouch2
FROM clients AS C
CROSS JOIN
(
SELECT *
FROM TFinal
WHERE ID = -1
)TF
) ALLROWS
ON TF.id=ALLROWS.ID
AND TF.Kind=ALLROWS.Kind
ORDER BY ISNULL(TF.id,ALLROWS.id) ,ISNULL(TF.kind,ALLROWS.kind)
[/code]
[code]
ID kind overWrite1 overWrite2 overWrite3 dontTouch1 dontTouch2
-1 1 a b c d e
-1 2 a b c nil nil
-1 3 a b c nil nil
1000 1 a b c x1 x2
1000 2 a b c x11 x22
1000 3 a b c nil nil
1001 1 a b c d e
1001 2 a b c nil nil
1001 3 a b c nil nil
[/code]
sabinWeb MCP |
 |
|
|
nirnir2
Starting Member
20 Posts |
Posted - 2014-07-01 : 08:24:43
|
Thanks,
How do I actually make those changes in table final |
|
|
|
stepson
Aged Yak Warrior
545 Posts |
Posted - 2014-07-01 : 08:40:38
|
1.
Truncate table TFinal
Insert Into TFinal(ID,kind,overWrite1,overWrite2,overWrite3,dontTouch1,dontTouch2)
SELECT ISNULL(TF.id,ALLROWS.id) AS ID
,ISNULL(TF.kind,ALLROWS.kind) AS kind
,ISNULL(ALLROWS.overWrite1,TF.overWrite1) AS overWrite1
,ISNULL(ALLROWS.overWrite2,TF.overWrite2) AS overWrite2
,ISNULL(ALLROWS.overWrite3,TF.overWrite3) AS overWrite3
,ISNULL(TF.dontTouch1,ALLROWS.dontTouch1) AS dontTouch1
,ISNULL(TF.dontTouch2,ALLROWS.dontTouch2) AS dontTouch2
or use MERGE
sabinWeb MCP |
|
|
|
stepson
Aged Yak Warrior
545 Posts |
Posted - 2014-07-01 : 08:50:39
|
for MERGE , something like this(not tested)
MERGE TFinal AS T
USING cteFinal(ID,kind,overWrite1,overWrite2,overWrite3,dontTouch1,dontTouch2) AS S
ON (T.id=S.id AND T.kind=S.kind)
WHEN MATCHED
THEN
UPDATE SET T.overWrite1=S.overWrite1
WHEN NOT MATCHED BY TRAGET
THEN INSERT (ID,kind,overWrite1,overWrite2,overWrite3,dontTouch1,dontTouch2)
VALUES(S.ID,S.kind,S.overWrite1,S.overWrite2,S.overWrite3,S.dontTouch1,S.dontTouch2)
;
where TFinal is your table
and cteFinal is the final select put in a CTE
sabinWeb MCP |
|
|
|
nirnir2
Starting Member
20 Posts |
Posted - 2014-07-01 : 10:11:19
|
| Thanks again |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2014-07-01 : 11:33:11
|
[code]DECLARE @Clients TABLE
(
id SMALLINT NOT NULL
);
INSERT @Clients
(
id
)
VALUES (1000),
(1001);
DECLARE @Final TABLE
(
id SMALLINT NOT NULL,
kind TINYINT NOT NULL,
overWrite1 VARCHAR(3),
overWrite2 VARCHAR(3),
overWrite3 VARCHAR(3),
dontTouch1 VARCHAR(3),
dontTouch2 VARCHAR(3)
);
INSERT @Final
(
id,
kind,
overWrite1,
overWrite2,
overWrite3,
dontTouch1,
dontTouch2
)
VALUES (-1, 1, 'a', 'b', 'c', 'd', 'e'),
(-1, 2, 'a', 'b', 'c', NULL, NULL),
(-1, 3, 'a', 'b', 'c', NULL, NULL),
(1000, 1, 'aaa', 'b', 'c', 'x1', 'x2'),
(1000, 2, 'a', 'bbb', 'c', 'x11', 'x22');
-- Before
SELECT *
FROM @Final;
-- SwePeso
WITH cteSource(id, kind, overWrite1, overWrite2, overWrite3, dontTouch1, dontTouch2)
AS (
SELECT c.id,
x.kind,
ISNULL(f.overWrite1, x.overWrite1) AS overWrite1,
ISNULL(f.overWrite2, x.overWrite2) AS overWrite2,
ISNULL(f.overWrite3, x.overWrite3) AS overWrite3,
ISNULL(f.dontTouch1, x.dontTouch1) AS dontTouch1,
ISNULL(f.dontTouch2, x.dontTouch2) AS dontTouch2
FROM @Clients AS c
INNER JOIN @Final AS x ON x.id = -1
LEFT JOIN @Final AS f ON f.id = c.id
AND f.kind = x.kind
)
MERGE @Final AS tgt
USING cteSource AS src ON src.id = tgt.id
AND src.kind = tgt.kind
WHEN NOT MATCHED BY TARGET
THEN INSERT (
id,
kind,
overWrite1,
overWrite2,
overWrite3,
dontTouch1,
dontTouch2
)
VALUES (
src.id,
src.kind,
src.overWrite1,
src.overWrite2,
src.overWrite3,
src.dontTouch1,
src.dontTouch2
);
-- After
SELECT *
FROM @Final;[/code]
Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA |
|
|
|
nirnir2
Starting Member
20 Posts |
Posted - 2014-07-02 : 07:22:52
|
| Thanks SwePeso |
|
|
|
|
|
|
update table