I cant delete records

Hi,

My DB is MS SQL Server. I wrote a SQL statement to remove rows from a table as following.

DELETE FROM #TableA
FROM #TableA AS t1
LEFT JOIN #TableA AS t2 ON (t1.Index - 1) = t2.Index
WHERE t2.Index IS NULL

========================================

When I tried to execute it, I got the following error message.
Error Message: The table '#TableA' is ambiguous.

Thanks for your help.Interesting problem, never thought about it. I can imagine that MSSQL has some problem with guessing what to do.

I know it is not an answer to your question, the following syntax should do the same (if I got the query right) :

DELETE FROM #TableA
WHERE Index-1 NOT IN (SELECT Index FROM #TableA)

ps. Do you really have columns named "Index"|||You have answered my question.
Many Thanks.

I don't have a column named "Index", it is a alias to simply my question.

i can use SQL2005 Client Tools with SQL2000?

hi
i can use SQL2005 Client Tools (lik vs2005) with SQL2000, to control and run
SQL Statement?
--
Best Regards
Tark M. Siala
Development Manager
INTERNATIONAL COMPUTER CENTER (ICC.Networking)
Mobile: +218-91-3125900
E-Mail: tarksiala@.icc-libya.com
Messenger: tarksiala@.hotmail.com
Web Page: http://www.icc-libya.com
Blog: http://spaces.msn.com/tarksiala
======================================Absolutely have been for 18 months now
--
Simon Sabin
SQL Server MVP
http://sqljunkies.com/weblog/simons
"Tark Siala" <tarksiala@.icc-libya.com> wrote in message
news:utkx4v%23YGHA.4620@.TK2MSFTNGP04.phx.gbl...
> hi
> i can use SQL2005 Client Tools (lik vs2005) with SQL2000, to control and
> run SQL Statement?
> --
> Best Regards
> Tark M. Siala
> Development Manager
> INTERNATIONAL COMPUTER CENTER (ICC.Networking)
> Mobile: +218-91-3125900
> E-Mail: tarksiala@.icc-libya.com
> Messenger: tarksiala@.hotmail.com
> Web Page: http://www.icc-libya.com
> Blog: http://spaces.msn.com/tarksiala
> ======================================>|||Definitely use Management Studio for all your databases, both SQL2005 and
SQL2000.
The only thing I think is a pain with it would be that when you generate the
script for an object, the drop statement isn't there. But it's not a
show-stopper, and you'll find that many of the features really help.
"Simon Sabin" wrote:
> Absolutely have been for 18 months now
> --
> Simon Sabin
> SQL Server MVP
> http://sqljunkies.com/weblog/simons
> "Tark Siala" <tarksiala@.icc-libya.com> wrote in message
> news:utkx4v%23YGHA.4620@.TK2MSFTNGP04.phx.gbl...
> > hi
> > i can use SQL2005 Client Tools (lik vs2005) with SQL2000, to control and
> > run SQL Statement?
> > --
> > Best Regards
> >
> > Tark M. Siala
> > Development Manager
> > INTERNATIONAL COMPUTER CENTER (ICC.Networking)
> > Mobile: +218-91-3125900
> > E-Mail: tarksiala@.icc-libya.com
> > Messenger: tarksiala@.hotmail.com
> > Web Page: http://www.icc-libya.com
> > Blog: http://spaces.msn.com/tarksiala
> > ======================================> >
>
>|||Is there an automatic completion feature ?|||SoftLion wrote:
> Is there an automatic completion feature ?
>
Do you mean Intellisense? Not built-in, but SQL Prompt (free download)
will give you that capability. Works with the 2000 client tools as well.
http://www.red-gate.com/

hw to add text in a 'select' statement

Hi Friends,
Could you tell me how to add text in a select statement.
For eg:
SELECT ((datepart(MM,CURRENT_TIMESTAMP)+ '-'
+(datepart(dd,CURRENT_TIMESTAMP) + '-' +
datepart(yyyy,CURRENT_TIMESTAMP))AS
"CURRENT_DATE"
..............ofcourse,this is not correct but it reflects my idea.
Thanks & Regards
Dutt
Seems you got the general idea. The problem is that DATEPART returns some integer value and you
cannot concatenate that with a string (the string will be converted to an int which will fail). So
you would have to do a CAST around each DATEPART call to make it some char or varchar. But check
first if there already exists a format for the CONVERT function that has the formatting you desire
(check the 3:rd parameter in Books Online).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Dutt" <Mr.Dutt@.gmail.com> wrote in message
news:1140021331.883831.135940@.g14g2000cwa.googlegr oups.com...
> Hi Friends,
> Could you tell me how to add text in a select statement.
> For eg:
> SELECT ((datepart(MM,CURRENT_TIMESTAMP)+ '-'
> +(datepart(dd,CURRENT_TIMESTAMP) + '-' +
> datepart(yyyy,CURRENT_TIMESTAMP))AS
> "CURRENT_DATE"
> .............ofcourse,this is not correct but it reflects my idea.
> Thanks & Regards
> Dutt
>
|||Thanks Tibor,
Its really useful link....It worked .

hw to add text in a 'select' statement

Hi Friends,
Could you tell me how to add text in a select statement.
For eg:
SELECT ((datepart(MM,CURRENT_TIMESTAMP)+ '-'
+(datepart(dd,CURRENT_TIMESTAMP) + '-' +
datepart(yyyy,CURRENT_TIMESTAMP))AS
"CURRENT_DATE"
.............ofcourse,this is not correct but it reflects my idea.
Thanks & Regards
DuttSeems you got the general idea. The problem is that DATEPART returns some in
teger value and you
cannot concatenate that with a string (the string will be converted to an in
t which will fail). So
you would have to do a CAST around each DATEPART call to make it some char o
r varchar. But check
first if there already exists a format for the CONVERT function that has the
formatting you desire
(check the 3:rd parameter in Books Online).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Dutt" <Mr.Dutt@.gmail.com> wrote in message
news:1140021331.883831.135940@.g14g2000cwa.googlegroups.com...
> Hi Friends,
> Could you tell me how to add text in a select statement.
> For eg:
> SELECT ((datepart(MM,CURRENT_TIMESTAMP)+ '-'
> +(datepart(dd,CURRENT_TIMESTAMP) + '-' +
> datepart(yyyy,CURRENT_TIMESTAMP))AS
> "CURRENT_DATE"
> .............ofcourse,this is not correct but it reflects my idea.
> Thanks & Regards
> Dutt
>|||Thanks Tibor,
Its really useful link....It worked .

hw to add text in a 'select' statement

Hi Friends,
Could you tell me how to add text in a select statement.
For eg:
SELECT ((datepart(MM,CURRENT_TIMESTAMP)+ '-'
+(datepart(dd,CURRENT_TIMESTAMP) + '-' +
datepart(yyyy,CURRENT_TIMESTAMP))AS
"CURRENT_DATE"
.............ofcourse,this is not correct but it reflects my idea.
Thanks & Regards
DuttSeems you got the general idea. The problem is that DATEPART returns some integer value and you
cannot concatenate that with a string (the string will be converted to an int which will fail). So
you would have to do a CAST around each DATEPART call to make it some char or varchar. But check
first if there already exists a format for the CONVERT function that has the formatting you desire
(check the 3:rd parameter in Books Online).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Dutt" <Mr.Dutt@.gmail.com> wrote in message
news:1140021331.883831.135940@.g14g2000cwa.googlegroups.com...
> Hi Friends,
> Could you tell me how to add text in a select statement.
> For eg:
> SELECT ((datepart(MM,CURRENT_TIMESTAMP)+ '-'
> +(datepart(dd,CURRENT_TIMESTAMP) + '-' +
> datepart(yyyy,CURRENT_TIMESTAMP))AS
> "CURRENT_DATE"
> .............ofcourse,this is not correct but it reflects my idea.
> Thanks & Regards
> Dutt
>|||Thanks Tibor,
Its really useful link....It worked .