update table - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

update table

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


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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources