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,GaryUPDATE 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 thisUPDATE t SET t.comments = 'MONDAY 092313 'FROM (SELECT comments,ROW_NUMBER() OVER (ORDER BY DATE_WORKED) AS RnFROM ud402.jd_mcp_masterWHERE QUEUE_NAME = 'JD_Testing')tWHERE Rn <= 25 ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
sigmas
Posting Yak Master
172 Posts |
Posted - 2013-09-23 : 13:19:33
|
UPDATE DSET 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 sugestionsasUPDATE t SET t.comments = 'MONDAY 092313 'FROM (SELECT comments,DENSE_RANK() OVER (ORDER BY DATE_WORKED) AS RnFROM ud402.jd_mcp_masterWHERE QUEUE_NAME = 'JD_Testing')tWHERE Rn <= 25 andUPDATE DSET comments = 'MONDAY 092313 ' FROM (SELECT TOP (25) WITH TIES commentsFROM ud402.jd_mcp_master WHERE QUEUE_NAME = 'JD_Testing'ORDER BY DATE_WORKED )D; ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|