Update from one table to another

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

General SQL Server Forums

New to SQL Server Programming

Update from one table to another

Author

Topic

GautamC
Starting Member

3 Posts

Posted - 2014-05-02 : 06:00:47

I'm a complete newbie to SQL Server (and to SQL in general), so please bear with me if the question sounds stupid or too basic.
My SQL Server version: 2008 Developer R2
I have 2 tables, comp1 and comp2 (imported from two separate Excel files), each consisting of 2 fields - 'name' and 'grp'. Both fields are varchar(5). The tables contain the following data (sample):

comp1
name grp
ab 1234
cd 2345
gh NULL
cd 5678
jk 0101
jk 0202
gh 8765
lm NULL

comp2
name grp
ab 1234
cd 2345
de 3456
ef 4567

I would like to update the comp2 table with data from the comp1 table, with the following conditions:
1) Only distinct 'name' values not already in comp2 to be added to comp2.
2) Update the corresponding 'grp' value in comp2 with the 1st non-null value from comp1 for the same 'name' value.
3) If the corresponding 'grp' values in comp1 are all null, then don't update that 'grp' value in comp2.

Essentially, for the sample data above, I would like to end up with:

comp2
name grp
ab 1234
cd 2345
de 3456
ef 4567
gh 8765
jk 0101
lm NULL

The closest I got to what I want is with the following code:

insert into comp2
select name,grp from (select comp1.name, comp1.grp from comp1
where comp1.name not in (select comp2.name from comp2)) as tmp

but this updates comp2 with duplicate values of the 'name' field, which I specifically wan't to avoid.

I would appreciate any help or suggestions.
Many thanks

Ifor
Aged Yak Warrior

700 Posts

Posted - 2014-05-02 : 06:36:16

[code]
-- *** Test Data ***
CREATE TABLE #comp1
(
name char(2) NOT NULL
,grp char(4) NULL
);
INSERT INTO #comp1
SELECT 'ab', '1234'
UNION ALL SELECT 'cd', '2345'
UNION ALL SELECT 'gh', NULL
UNION ALL SELECT 'cd', '5678'
UNION ALL SELECT 'jk', '0101'
UNION ALL SELECT 'jk', '0202'
UNION ALL SELECT 'gh', '8765'
UNION ALL SELECT 'lm', NULL;

CREATE TABLE #comp2
(
name char(2) NOT NULL
,grp char(4) NULL
);
INSERT INTO #comp2
SELECT 'ab', '1234'
UNION ALL SELECT 'cd', '2345'
UNION ALL SELECT 'de', '3456'
UNION ALL SELECT 'ef', '4567';
-- *** End Test Data ***

INSERT INTO #comp2
-- A table is an unordered set so FIRST has no meaning unless an order is explicitly mentioned.
SELECT name, MIN(grp)
FROM #comp1 C1
WHERE NOT EXISTS
(
SELECT 1
FROM #comp2 C2
WHERE C2.name = C1.name
)
GROUP BY name;

select * from #comp2 order by name;
[/code]

Tusharp86
Starting Member

9 Posts

Posted - 2014-05-02 : 07:07:58

1. Step :

insert into #comp2
select distinct name,Null from #comp1 where name not in (select name from #comp2)

2. Step :

;WITH cte AS
(
SELECT *,
ROW_NUMBER() OVER (PARTITION BY name order by name ) AS rn
FROM #comp1
),

cte2 AS
(
SELECT *,
ROW_NUMBER() OVER (PARTITION BY name order by name ) AS rn
FROM #comp1
)
--select cte.*,cte2.* from cte inner join cte2 on cte.name = cte2.name and ( cte.rn = 1 and cte2.rn = 2)
update #comp2 set #comp2.grp =cte2.grp from #comp2 inner join
(cte inner join cte2 on cte.name = cte2.name and ( cte.rn = 1 and cte2.rn = 2)) on cte.name = #comp2.name

Here I used # table

MuralikrishnaVeera
Posting Yak Master

129 Posts

Posted - 2014-05-02 : 07:27:13

similar to ifor but a little change


INSERT INTO #comp2
SELECT DISTINCT  name,MIN(grp)OVER (PARTITION BY name) as grp FROM 
#comp1 a WHERE  NOT EXISTS (SELECT 1 FROM #comp2 WHERE name =a.name)

select * from #comp2

---------------
Murali Krishna

You live only once ..If you do it right once is enough.......

GautamC
Starting Member

3 Posts

Posted - 2014-05-02 : 07:54:14

Thanks a lot, Ifor! That worked perfectly - though I will need some time to figure out how.

quote:

Originally posted by Ifor


-- *** Test Data ***
CREATE TABLE #comp1
(
	name char(2) NOT NULL
	,grp char(4) NULL
);
INSERT INTO #comp1
SELECT 'ab', '1234'
UNION ALL SELECT 'cd', '2345'
UNION ALL SELECT 'gh', NULL
UNION ALL SELECT 'cd', '5678'
UNION ALL SELECT 'jk', '0101'
UNION ALL SELECT 'jk', '0202'
UNION ALL SELECT 'gh', '8765'
UNION ALL SELECT 'lm', NULL;

CREATE TABLE #comp2
(
	name char(2) NOT NULL
	,grp char(4) NULL
);
INSERT INTO #comp2
SELECT 'ab', '1234'
UNION ALL SELECT 'cd', '2345'
UNION ALL SELECT 'de', '3456'
UNION ALL SELECT 'ef', '4567';
-- *** End Test Data ***

INSERT INTO #comp2
-- A table is an unordered set so FIRST has no meaning unless an order is explicitly mentioned.
SELECT name, MIN(grp)
FROM #comp1 C1
WHERE NOT EXISTS
(
	SELECT 1
	FROM #comp2 C2
	WHERE C2.name = C1.name
)
GROUP BY name;

select * from #comp2 order by name;

GautamC
Starting Member

3 Posts

Posted - 2014-05-02 : 08:03:05

Thanks, MuraliKrishnaVeera - that worked exactly as desired. Again, as with Ifor's code, I will need to progress quite a bit further up the T-SQL learning curve to be able to decipher the inner workings of the code.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources