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
 General SQL Server Forums
 New to SQL Server Programming
 Update Top 25
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

gelwood
Starting Member

USA
8 Posts

Posted - 09/23/2013 :  13:02:39  Show Profile  Reply with Quote
Hello!
I'm having a problem with the syntax for doing an update to the top 25 records in my table....
This is what i have.... Any Ideas????
Thanks,
Gary

UPDATE TOP (25) FROM ud402.jd_mcp_master SET comments = 'MONDAY 092313 ' WHERE QUEUE_NAME = 'JD_Testing' ORDER BY DATE_WORKED ;

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 09/23/2013 :  13:07:58  Show Profile  Reply with Quote
should be this


UPDATE t
 SET t.comments = 'MONDAY 092313 '
FROM (
SELECT comments,ROW_NUMBER() OVER (ORDER BY DATE_WORKED) AS Rn
FROM ud402.jd_mcp_master
WHERE QUEUE_NAME = 'JD_Testing'
)t
WHERE Rn <= 25


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

sigmas
Posting Yak Master

Belarus
172 Posts

Posted - 09/23/2013 :  13:19:33  Show Profile  Reply with Quote
UPDATE D
SET comments = 'MONDAY 092313 '
FROM
(
SELECT TOP (25) *
FROM ud402.jd_mcp_master
WHERE QUEUE_NAME = 'JD_Testing'
ORDER BY DATE_WORKED
)D;

Edited by - sigmas on 09/23/2013 13:21:11
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 09/23/2013 :  13:48:27  Show Profile  Reply with Quote
One more thing. if you've multiple records for same date and want to cover all within top 25 dates you need to tweak sugestions
as


UPDATE t
 SET t.comments = 'MONDAY 092313 '
FROM (
SELECT comments,DENSE_RANK() OVER (ORDER BY DATE_WORKED) AS Rn
FROM ud402.jd_mcp_master
WHERE QUEUE_NAME = 'JD_Testing'
)t
WHERE Rn <= 25


and


UPDATE D
SET comments = 'MONDAY 092313 ' 
FROM 
(
SELECT TOP (25) WITH TIES comments
FROM ud402.jd_mcp_master 
WHERE QUEUE_NAME = 'JD_Testing'
ORDER BY DATE_WORKED 
)D;


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

gelwood
Starting Member

USA
8 Posts

Posted - 09/24/2013 :  09:30:19  Show Profile  Reply with Quote

Thanks.... but still not working. I get the following error.


UPDATE Failed. 3706: Syntax error: expected something between a string or a Unicode character literal and the “FROM” keyword.
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3707 Posts

Posted - 09/24/2013 :  09:42:14  Show Profile  Reply with Quote
quote:
Originally posted by gelwood


Thanks.... but still not working. I get the following error.


UPDATE Failed. 3706: Syntax error: expected something between a string or a Unicode character literal and the “FROM” keyword.

The queries posted earlier look like they are valid T-SQL queries. Are you using Microsoft SQL Server? The text of that error message doesn't sound familiar.

Go to Top of Page

gelwood
Starting Member

USA
8 Posts

Posted - 09/24/2013 :  13:56:04  Show Profile  Reply with Quote
No sorry...! It's not a SQL database.... It's Teradata.
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3707 Posts

Posted - 09/24/2013 :  15:10:12  Show Profile  Reply with Quote
There are very few, if any, who are experts on Teradata. You might get better responses at a forum specific to Teradata.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 09/25/2013 :  07:48:21  Show Profile  Reply with Quote
quote:
Originally posted by gelwood

No sorry...! It's not a SQL database.... It's Teradata.


Try in some Teradata forums

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
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