from the table they're pulled from:
SELECT A.* FROM SalesOrderPartPrices A
WHERE EXISTS(
SELECT 'Exists' FROM SalesOrderPartPrices B
WHERE
(A.SalesOrderNo = B.SalesOrderNo) AND
(A.PartNo = B.PartNo) AND
(A.UnitPrice = B.UnitPrice) AND
(A.RowID > B.RowID)
)
However, when I try to run the following query, I get an error ("Msg
170, Level 15, State 1, Line 1: Incorrect syntax near 'A'."):
DELETE FROM SalesOrderPartPrices A
WHERE EXISTS(
SELECT 'Exists' FROM SalesOrderPartPrices B
WHERE
(A.SalesOrderNo = B.SalesOrderNo) AND
(A.PartNo = B.PartNo) AND
(A.UnitPrice = B.UnitPrice) AND
(A.RowID > B.RowID)
)
What am I doing wrong, and how do I resolve the issue? Any help is
greatly appreciated...... thanks in advance!
-=Tek Boy=-You don't understand the SQL model for tables -- base, derived, temp,
views, etc. A correlation name acts as if it creates a new table with
the new name and the old data. That working table then disappears at
the end of the statement. If you want to change a base table, you
have to use a base table name:
DELETE FROM SalesOrderPartPrices
WHERE EXISTS(
SELECT *
FROM SalesOrderPartPrices AS S1
WHERE SalesOrderPartPrices.SalesOrderNo = S1.SalesOrderNo
AND SalesOrderPartPrices.PartNo = S1.PartNo
AND SalesOrderPartPrices.UnitPrice = S1.UnitPrice
AND SalesOrderPartPrices.RowID > S1.RowID):
Is rowid actually a PHYSICAL location in the PHYSICAL table, a la
Oracle? Are you trying to remove dups because your table was created
without keys? If so, I hope you get to run over the moron that did
this in the parking lot. You are going to have to re-do all the
reports run against this table for at least the last year because of
him. Then you have to introduce a real key, etc.
From the way you wrote your code, I would guess that SQL is not your
main language and that you are an old mainframer. You were using too
many parens and putting AND at the end of the line like we did with
punch cards.
While that kind of formatting does not mess up the compiler, it will
increase the time to maintain code by 8-10% because of the way people
read from left to right in English.|||Thanks for your reply, Joe. You are correct in your observation that
SQL is not my primarily language -- I develop mainly using ASP, HTML,
Javascript (and eventually C#/.NET). As such, my understanding of the
foundations of SQL is still rather limited -- however, I do have a
copy of "SQL for Smarties: 2nd Ed." sitting beside me, so hopefully
that situation will improve over time. As far as being an "old
mainframer", I've only read about mainframes in books and such. :)
My hands-on experience has been limited to Access, SQL Server and
(briefly) Oracle 8i.
My intention here was to clean up a bunch of data exported from a
MANMAN database, for subsequent use by a SQL Server 2000-drive web
application; no live data was harmed in the processing of these
scripts. And the only reason I added the RowID (identity) column is
because all of the other SQL Server methods I've read about for
removing duplicate rows requires some type of temp table. The most
elegant method I managed to find in the newsgroups was written for
Oracle, and made use of a RowID -- I couldn't create UNIQUE or PRIMARY
KEY constraints initially because of the duplicates. After I cleaned
everything up, I deleted the RowID column and created a PRIMARY KEY
constraint on (SalesOrder, PartNo).
Since you mention code formatting -- is there a method you DO suggest?
I haven't found a clearly-documented method of writing scripts that I
find easy to read AND doesn't take up inordinate amount of real estate
on my monitor. Always looking for a better way to do
things.........
-=Tek Boy=-|||Speaking of formatting, if anybody from MS is listening, I would LOVE
to be able to set the width of the tab in Query Analyzer as is
possible in the VS IDE.
On 11 Feb 2004 13:32:22 -0800, vejitaasp@.hotmail.com (Saiyan Vejita)
wrote:
>Thanks for your reply, Joe. You are correct in your observation that
>SQL is not my primarily language -- I develop mainly using ASP, HTML,
>Javascript (and eventually C#/.NET). As such, my understanding of the
>foundations of SQL is still rather limited -- however, I do have a
>copy of "SQL for Smarties: 2nd Ed." sitting beside me, so hopefully
>that situation will improve over time. As far as being an "old
>mainframer", I've only read about mainframes in books and such. :)
>My hands-on experience has been limited to Access, SQL Server and
>(briefly) Oracle 8i.
>My intention here was to clean up a bunch of data exported from a
>MANMAN database, for subsequent use by a SQL Server 2000-drive web
>application; no live data was harmed in the processing of these
>scripts. And the only reason I added the RowID (identity) column is
>because all of the other SQL Server methods I've read about for
>removing duplicate rows requires some type of temp table. The most
>elegant method I managed to find in the newsgroups was written for
>Oracle, and made use of a RowID -- I couldn't create UNIQUE or PRIMARY
>KEY constraints initially because of the duplicates. After I cleaned
>everything up, I deleted the RowID column and created a PRIMARY KEY
>constraint on (SalesOrder, PartNo).
>Since you mention code formatting -- is there a method you DO suggest?
> I haven't found a clearly-documented method of writing scripts that I
>find easy to read AND doesn't take up inordinate amount of real estate
>on my monitor. Always looking for a better way to do
>things.........
>
>-=Tek Boy=-|||>> My intention here was to clean up a bunch of data exported from a
MANMAN database, .. <<
Okay, this is not **real** data, but only a scrubbing table! Have you
looked at any ETL tools for this job? They are geared for this kind
of thing and havea lot of extras that can be helpful. Otherwise, use
IDENTITY or a sequential file for the scrub work.
>> Since you mention code formatting -- is there a method you DO
suggest? <<
My rules have been picked up by my various publishers, and they are:
1) Uppercase keywords and put one per line
2) lowercase scalars, like column names, local variables, etc.
3) Capitalize schema objects like table names, view names, etc.
4) Indent along a gutter for each subquery or clause
And follow ISO-11179 Standard nbaming conventions.|||In Query Analyzer, open Tools | Options then on the Editor tab see Tab size
(in spaces). Is that what you meant?
"Ellen K." <72322.enno.esspeeayem.1016@.compuserve.com> wrote in message
news:p0a03050ott4cf0claq65cfoi8b1v0oco7@.4ax.com...
> Speaking of formatting, if anybody from MS is listening, I would LOVE
> to be able to set the width of the tab in Query Analyzer as is
> possible in the VS IDE.
> On 11 Feb 2004 13:32:22 -0800, vejitaasp@.hotmail.com (Saiyan Vejita)
> wrote:
> >Thanks for your reply, Joe. You are correct in your observation that
> >SQL is not my primarily language -- I develop mainly using ASP, HTML,
> >Javascript (and eventually C#/.NET). As such, my understanding of the
> >foundations of SQL is still rather limited -- however, I do have a
> >copy of "SQL for Smarties: 2nd Ed." sitting beside me, so hopefully
> >that situation will improve over time. As far as being an "old
> >mainframer", I've only read about mainframes in books and such. :)
> >My hands-on experience has been limited to Access, SQL Server and
> >(briefly) Oracle 8i.
> >My intention here was to clean up a bunch of data exported from a
> >MANMAN database, for subsequent use by a SQL Server 2000-drive web
> >application; no live data was harmed in the processing of these
> >scripts. And the only reason I added the RowID (identity) column is
> >because all of the other SQL Server methods I've read about for
> >removing duplicate rows requires some type of temp table. The most
> >elegant method I managed to find in the newsgroups was written for
> >Oracle, and made use of a RowID -- I couldn't create UNIQUE or PRIMARY
> >KEY constraints initially because of the duplicates. After I cleaned
> >everything up, I deleted the RowID column and created a PRIMARY KEY
> >constraint on (SalesOrder, PartNo).
> >Since you mention code formatting -- is there a method you DO suggest?
> > I haven't found a clearly-documented method of writing scripts that I
> >find easy to read AND doesn't take up inordinate amount of real estate
> >on my monitor. Always looking for a better way to do
> >things.........
> >-=Tek Boy=-|||Actually I misspoke, I didn't mean Query Analyzer, I meant the
workspace for writing a stored procedure over in Enterprise Manager.
On Fri, 07 May 2004 16:30:43 GMT, "tperovic" <tonyperovic@.yahoo.com>
wrote:
>In Query Analyzer, open Tools | Options then on the Editor tab see Tab size
>(in spaces). Is that what you meant?
>"Ellen K." <72322.enno.esspeeayem.1016@.compuserve.com> wrote in message
>news:p0a03050ott4cf0claq65cfoi8b1v0oco7@.4ax.com...
>> Speaking of formatting, if anybody from MS is listening, I would LOVE
>> to be able to set the width of the tab in Query Analyzer as is
>> possible in the VS IDE.
>>
>> On 11 Feb 2004 13:32:22 -0800, vejitaasp@.hotmail.com (Saiyan Vejita)
>> wrote:
>>
>> >Thanks for your reply, Joe. You are correct in your observation that
>> >SQL is not my primarily language -- I develop mainly using ASP, HTML,
>> >Javascript (and eventually C#/.NET). As such, my understanding of the
>> >foundations of SQL is still rather limited -- however, I do have a
>> >copy of "SQL for Smarties: 2nd Ed." sitting beside me, so hopefully
>> >that situation will improve over time. As far as being an "old
>> >mainframer", I've only read about mainframes in books and such. :)
>> >My hands-on experience has been limited to Access, SQL Server and
>> >(briefly) Oracle 8i.
>>> >My intention here was to clean up a bunch of data exported from a
>> >MANMAN database, for subsequent use by a SQL Server 2000-drive web
>> >application; no live data was harmed in the processing of these
>> >scripts. And the only reason I added the RowID (identity) column is
>> >because all of the other SQL Server methods I've read about for
>> >removing duplicate rows requires some type of temp table. The most
>> >elegant method I managed to find in the newsgroups was written for
>> >Oracle, and made use of a RowID -- I couldn't create UNIQUE or PRIMARY
>> >KEY constraints initially because of the duplicates. After I cleaned
>> >everything up, I deleted the RowID column and created a PRIMARY KEY
>> >constraint on (SalesOrder, PartNo).
>>> >Since you mention code formatting -- is there a method you DO suggest?
>> > I haven't found a clearly-documented method of writing scripts that I
>> >find easy to read AND doesn't take up inordinate amount of real estate
>> >on my monitor. Always looking for a better way to do
>> >things.........
>>>>> >-=Tek Boy=-
>
No comments:
Post a Comment