simple update but confused - 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)

simple update but confused

Author

Topic

mike13
Posting Yak Master

219 Posts

Posted - 2013-04-19 : 06:28:57

Hi All,

I got this table
[T_Keyword](
[id] [int] IDENTITY(1,1) NOT NULL,
[sitename] [nvarchar](255) NULL,
[List_name] [nvarchar](50) NULL,
[keyword] [nvarchar](max) NULL,

i got 2 records for each sitename ex:

1 domain.com level1 keyword1
2 domain.com level2 keyword2

I want to update the keyword field which named 'level1' with the one 'level2'

This for each sitename in table

Thanks a lot

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-04-19 : 06:59:16

-- Simple illustration for the above update

DECLARE @T_Keyword TABLE( [id] [int] NOT NULL,[sitename] [nvarchar](255) NULL, [List_name] [nvarchar](50) NULL,[keyword] [nvarchar](max) NULL)
--i got 2 records for each sitename ex:
INSERT INTO @T_Keyword
SELECT 1, 'domain.com', 'level1', 'keyword1' union all
SELECT 2, 'domain.com', 'level2', 'keyword2' union all
SELECT 3, 'domain1.com', 'level1', 'keyword11' union all
SELECT 4, 'domain1.com', 'level2', 'keyword12'
--I want to update the keyword field which named 'level1' with the one 'level2'
;WITH cte
 AS
( SELECT *, ROW_NUMBER() OVER(PARTITION BY sitename ORDER BY List_name) RN FROM @T_Keyword )
UPDATE c1
SET c1.keyword = c2.keyword
FROM cte c1 
JOIN cte c2 ON c1.Rn = c2.Rn-1 and c1.sitename = c2.sitename

SELECT * FROM @T_Keyword

--
Chandu

mike13
Posting Yak Master

219 Posts

Posted - 2013-04-19 : 07:03:23

Thanks a lot Chandu,

i got one server that is running 2005, how do i do it on that server?

tnx a lot

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-04-19 : 07:36:19

Simply you can run the above code by changing Table Name (@T_Keywod to T_Keyword)

;WITH cte
 AS
( SELECT *, ROW_NUMBER() OVER(PARTITION BY sitename ORDER BY List_name) RN FROM T_Keyword )
UPDATE c1
SET c1.keyword = c2.keyword
FROM cte c1 
JOIN cte c2 ON c1.Rn = c2.Rn-1 and c1.sitename = c2.sitename

SELECT * FROM T_Keyword

--
Chandu

mike13
Posting Yak Master

219 Posts

Posted - 2013-04-19 : 07:37:31

Over does not work on 2005

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-04-19 : 07:40:32

OVER() clause will work in MSSQL 2005
see this document
http://msdn.microsoft.com/en-in/library/ms189461(v=sql.90).aspx

--
Chandu

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-04-19 : 07:51:21

--Check compatibility level
select compatibility_level from sys.databases where name=db_name()

If this is 80, change it to 90
----set compatible level to SQL Server 2005
EXEC sp_dbcmptlevel YourDatabaseName, 90;
GO
Then run the above query

Support document: http://blog.sqlauthority.com/2007/05/29/sql-server-2005-change-database-compatible-level-backward-compatibility/

--
Chandu

mike13
Posting Yak Master

219 Posts

Posted - 2013-04-19 : 09:38:00

Great that did the trick!

Thanks a lot

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-04-19 : 09:41:42