Update Top 25 - 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

General SQL Server Forums

New to SQL Server Programming

Update Top 25

Author

Topic

gelwood
Starting Member

8 Posts

Posted - 2013-09-23 : 13:02:39

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

52326 Posts

Posted - 2013-09-23 : 13:07:58

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

sigmas
Posting Yak Master

172 Posts

Posted - 2013-09-23 : 13:19:33

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;

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-09-23 : 13:48:27

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

gelwood
Starting Member

8 Posts

Posted - 2013-09-24 : 09:30:19

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.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-09-24 : 09:42:14

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.

gelwood
Starting Member

8 Posts

Posted - 2013-09-24 : 13:56:04

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

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-09-24 : 15:10:12

There are very few, if any, who are experts on Teradata. You might get better responses at a forum specific to Teradata.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-09-25 : 07:48:21

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources