CODEWITHSUNDEEP
sqlsql-servert-sql
mariocatch
mariocatch

Reputation: 8703

How to insert into Table B, all rows from Table B, but with one column changed

I have a table that I'm trying to duplicate every row of, but with one column having a different value. The problem is getting that value involves another table which has no current relation.

Table Search:

SearchID Name
1 A 1
2 B 1
3 C 1
4 A 2
5 B 2
6 C 2

Table SearchFields:

SearchFieldID SearchID Foo
1 1 bob
2 1 mary
3 2 tim
4 2 justin
5 3 jay
6 3 anthony

What I'm trying to get is Table SearchFields to look like this:

Table Expected SearchFields:

SearchFieldID SearchID Foo
1 1 bob
2 1 mary
3 2 tim
4 2 justin
5 3 jay
6 3 anthony
7 4 bob
8 4 mary
9 5 tim
10 5 justin
11 6 jay
12 6 anthony

The only relation I can see is part of the Name column on Search (everything up to the last space gives me duplicate rows, and I can take the higher SearchId from each duplicate to give me the new SearchId to use)

So far I've been able to do things like:

select 
    max(search.searchid) [SearchId],
    substring(  name, 
                1, 
                len(name) - charindex(' ', reverse(name))) [Name] 
from search
group by 
    substring( name, 
                1, 
                len(name) - charindex(' ', reverse(name)))

This gives me

SearchID Name
4 A
5 B
6 C

But I'm not sure how I can take those results and map them to SearchFields, to do something like:

;with cte (searchid, name)
as
(
select 
    max(search.searchid) [SearchId],
    substring(  name, 
                1, 
                len(name) - charindex(' ', reverse(name))) [Name] 
from search
group by 
    substring( name, 
                1, 
                len(name) - charindex(' ', reverse(name)))
)
-- this part obviously wrong, not sure how to connect the two
insert into SearchFields(SearchId, Foo)
select c.SearchId, sf.Foo from cte c
union all
select Foo from SearchFields sf

Upvotes: 0

Views: 59

Answers (1)

mariocatch
mariocatch

Reputation: 8703

Think I figured it out.

insert into searchfields(searchid, Foo)
select 
(
select top 1 searchid from search where searchid = 
    (select top 1 max(s.searchid) 
     from search s 
     group by 
     substring(name, 1, len(name) - charindex(' ', reverse(name))) 
     having sf.searchid = min(s.searchid))
) [SearchId], Foo
from searchfields sf
order by searchid

Upvotes: 0

Related Questions