October 30, 2007 at 11:17 am
Hi,
I am trying to write an update statement that will concatenate 2 fields and add an incrementing number. Something like this....
UPDATE Table1
SET Email = Col1+'.'+Col2+(Incrementing number here)+@email.com
But I am not sure how to set the incrementing number :unsure:
Any help is greatly appreciated.
October 30, 2007 at 11:34 am
You can achieve it by using variables in an UPDATE statement like
DECLARE @Counter INT
SET @Counter = 0
UPDATE Table1
SET @Counter = @Counter + 1, Email = Col1+'.'+Col2+ CAST(@Counter AS VARCHAR(10) )+'@email.com' --You missed the quotation over here
You can also achieve this by using ROW_NUMBER() function (new in 2K5)
--Ramesh
October 30, 2007 at 12:59 pm
This works:
create table #t
(Col1 nvarchar(50))
GO
create table #s
(Col1 nvarchar(24)
,Col2 nvarchar(24)
)
GO
insert into #t (col1) values ('a')
insert into #t (col1) values ('b')
insert into #t (col1) values ('c')
insert into #t (col1) values ('d')
insert into #t (col1) values ('e')
insert into #t (col1) values ('f')
insert into #t (col1) values ('g')
insert into #t (col1) values ('h')
insert into #t (col1) values ('i')
insert into #t (col1) values ('j')
GO
INSERT INTO #s (col1,col2) values ('a','A')
INSERT INTO #s (col1,col2) values ('b','B')
INSERT INTO #s (col1,col2) values ('c','C')
INSERT INTO #s (col1,col2) values ('d','D')
INSERT INTO #s (col1,col2) values ('e','E')
INSERT INTO #s (col1,col2) values ('f','F')
INSERT INTO #s (col1,col2) values ('g','G')
INSERT INTO #s (col1,col2) values ('h','H')
INSERT INTO #s (col1,col2) values ('i','I')
INSERT INTO #s (col1,col2) values ('j','J')
GO
--UPDATE #t
--SET t.Col1
;WITH c AS (SELECT col1
,col2
,ROW_NUMBER() OVER (ORDER BY col1) AS col3
FROM #s)
UPDATE #t
SET Col1 = c.Col1 + c.Col2 + CAST(c.Col3 as VARCHAR)
FROM c
where #t.Col1 = c.Col1
GO
select * from #t
drop table #t
drop table #s
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 31, 2007 at 2:56 am
Thank you. Both ways work a treat. 😀
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply