[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.sqlserver.programming

Update with Sub-Select

mnd3

3/30/2007 8:56:00 PM

Hi, this should be an easy one for most you people but it is eluding
me. I am trying to do an update using a subselect and it errors out
saying it doesn't like my ORDER BY.

UPDATE(webapps.dbo.presidents_blog)
SET archive = 'yes', archive_date = '3/30/2007 3:03:12 PM'
WHERE topic_id NOT IN (SELECT Top 1 topic_id FROM
webapps.dbo.presidents_blog ORDER BY topic_id DESC)
AND archive IS NULL

Thanks for you help,
Mike
2 Answers

David Portas

3/30/2007 9:29:00 PM

0

On 30 Mar, 21:56, "mnd3" <Mike.Dar...@Avila.edu> wrote:
> Hi, this should be an easy one for most you people but it is eluding
> me. I am trying to do an update using a subselect and it errors out
> saying it doesn't like my ORDER BY.
>
> UPDATE(webapps.dbo.presidents_blog)
> SET archive = 'yes', archive_date = '3/30/2007 3:03:12 PM'
> WHERE topic_id NOT IN (SELECT Top 1 topic_id FROM
> webapps.dbo.presidents_blog ORDER BY topic_id DESC)
> AND archive IS NULL
>
> Thanks for you help,
> Mike

The problem is the brackets around the table name. You don't need
them.

You might also improve upon the performance by rewriting the logic
slightly. Always use the standard format for dates and times. I'm
assuming archive_date is a DATETIME because you didn't post any DDL.

UPDATE webapps.dbo.presidents_blog
SET archive = 'yes',
archive_date = '2007-03-30T15:03:12.000'
WHERE topic_id <
(SELECT MAX(topic_id)
FROM webapps.dbo.presidents_blog)
AND archive IS NULL;

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/librar...(en-US,SQL.90).aspx
--

mnd3

3/31/2007 6:05:00 AM

0

>
> The problem is the brackets around the table name. You don't need
> them.
>
> You might also improve upon the performance by rewriting the logic
> slightly. Always use the standard format for dates and times. I'm
> assuming archive_date is a DATETIME because you didn't post any DDL.
>
> UPDATE webapps.dbo.presidents_blog
> SET archive = 'yes',
> archive_date = '2007-03-30T15:03:12.000'
> WHERE topic_id <
> (SELECT MAX(topic_id)
> FROM webapps.dbo.presidents_blog)
> AND archive IS NULL;
>
> --
> David Portas, SQL Server MVP
>
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
>
> SQL Server Books Online:http://msdn2.microsoft.com/librar...(en-US,SQL.90).aspx
> --

Thanks David, I noticed the bracket issue right before I left work for
the day. My thinking was that Query Analyzer had put them there so I
didn't pay attention to them. Yes, that pretty much sums up my SQL
skills. You are correct, the archive_date is a datetime field so
thanks for the other tip as well.
Thanks again for your help,
Have a great weekend,
Mike