Subselect question and much more

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 2005 Forums

Transact-SQL (2005)

Subselect question and much more

Author

Topic

LacOniC
Starting Member

29 Posts

Posted - 2008-02-20 : 10:54:27

I have two tables include similar columns. I want to add Table1 a value from Table2 but if it's duplicated i want to add a number to it.

FirstName (Column Of Table1 Includes)
John
John1
George
George2
George3
Michael

---

FirstName (Column Of Table2 Includes)
John

---

SELECT FirstName FROM Table2

Returns John

---

I want query returns John as John2 because John and John1 are already in Table1.

Note: I asked a similar question recent days but this is so different. Can't use row numbers.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-20 : 11:02:57

[code]INSERT INTO Table1 (Name)
SELECT t.FirstName +
CASE WHEN t.RowNo=0 THEN '' ELSE CAST(t.RowNo AS varchar(4)) END
FROM
(SELECT (ROW_NUMBER() OVER(PARTITION BY tmp.FirstName ORDER BY tmp.FirstName) -1) AS RowNo,
tmp.FirstName,
tmp.Source
FROM
(SELECT FirstName,'Table1' AS Source FROM Table1
UNION ALL
SELECT FirstName,'Table2' AS Source FROM Table2
)tmp
)t
WHERE t.Source='Table2'[/code]

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-02-20 : 11:30:22

[code]
declare @t1 table
(
a varchar(20)
)

declare @t2 table
(
b varchar(10)
)

insert @t1
select 'John' union all
select 'John1' union all
select 'George' union all
select 'George1' union all
select 'George2' union all
select 'Michael'

insert @t2
select 'John' union all
select 'George' union all
select 'Michael'

select b + cast(lastval+1 as varchar(2))
from
(
select t2.b, cast(max(
case
when patindex('%[0-9]%', a) > 0 then substring(a, patindex('%[0-9]%', a), len(a))
else 0
end) as int) as lastval
from @t1 t1 join @t2 t2 on t1.a like t2.b + '%'
group by t2.b
) t[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources