SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 simple update but confused
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mike13
Posting Yak Master

Netherlands
214 Posts

Posted - 04/19/2013 :  06:28:57  Show Profile  Click to see mike13's MSN Messenger address  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
2218 Posts

Posted - 04/19/2013 :  06:59:16  Show Profile  Reply with Quote
-- 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
Go to Top of Page

mike13
Posting Yak Master

Netherlands
214 Posts

Posted - 04/19/2013 :  07:03:23  Show Profile  Click to see mike13's MSN Messenger address  Reply with Quote
Thanks a lot Chandu,

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


tnx a lot
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2218 Posts

Posted - 04/19/2013 :  07:36:19  Show Profile  Reply with Quote
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
Go to Top of Page

mike13
Posting Yak Master

Netherlands
214 Posts

Posted - 04/19/2013 :  07:37:31  Show Profile  Click to see mike13's MSN Messenger address  Reply with Quote
Over does not work on 2005
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2218 Posts

Posted - 04/19/2013 :  07:40:32  Show Profile  Reply with Quote
OVER() clause will work in MSSQL 2005
see this document
http://msdn.microsoft.com/en-in/library/ms189461(v=sql.90).aspx

--
Chandu
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2218 Posts

Posted - 04/19/2013 :  07:51:21  Show Profile  Reply with Quote
--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
Go to Top of Page

mike13
Posting Yak Master

Netherlands
214 Posts

Posted - 04/19/2013 :  09:38:00  Show Profile  Click to see mike13's MSN Messenger address  Reply with Quote
Great that did the trick!

Thanks a lot
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2218 Posts

Posted - 04/19/2013 :  09:41:42  Show Profile  Reply with Quote
quote:
Originally posted by mike13

Great that did the trick!
Thanks a lot


Welcome

--
Chandu
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000