473,836 Members | 2,358 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

DELETE query syntax using subqueries

I have the following SELECT query, the results of which I would delete
from the table they're pulled from:

SELECT A.* FROM SalesOrderPartP rices A
WHERE EXISTS(
SELECT 'Exists' FROM SalesOrderPartP rices 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 SalesOrderPartP rices A
WHERE EXISTS(
SELECT 'Exists' FROM SalesOrderPartP rices 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=-
Jul 20 '05 #1
6 35418
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 SalesOrderPartP rices
WHERE EXISTS(
SELECT *
FROM SalesOrderPartP rices AS S1
WHERE SalesOrderPartP rices.SalesOrde rNo = S1.SalesOrderNo
AND SalesOrderPartP rices.PartNo = S1.PartNo
AND SalesOrderPartP rices.UnitPrice = S1.UnitPrice
AND SalesOrderPartP rices.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.
Jul 20 '05 #2
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=-
Jul 20 '05 #3
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, ve*******@hotma il.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=-


Jul 20 '05 #4
>> 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.
Jul 20 '05 #5
In Query Analyzer, open Tools | Options then on the Editor tab see Tab size
(in spaces). Is that what you meant?

"Ellen K." <72************ ************@co mpuserve.com> wrote in message
news:p0******** *************** *********@4ax.c om...
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, ve*******@hotma il.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=-

Jul 20 '05 #6
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" <to*********@ya hoo.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." <72************ ************@co mpuserve.com> wrote in message
news:p0******* *************** **********@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, ve*******@hotma il.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=-


Jul 20 '05 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
11210
by: Joe Del Medico | last post by:
I have two tables A & B and I want to delete all the records in A that are not in B. Can I do this in the query builder? It seems like a simple problem. I can easily find the records in A that aren't in B by using the query wizard to come up with a query "A without matching B". I added table A to the query builder and Query "A without matching B" joined them with the index field (Loan Number) made the query a delete query and
6
1956
by: Mark Reed | last post by:
Hi all, Please help. I have a table with 2 fields of which I am trying to change a select query into a delete query. the select query is: SELECT Table1.Date, Min(Table1.Ball) AS MinOfBall FROM Table1 GROUP BY Table1.Date;
1
1676
by: Thompson Yip | last post by:
The following delete query: DoCmd.RunSQL "delete * from where ='" & strInput & "'" and DoCmd.RunSQL "delete from where ='" & strInput & "'" Are they equivalent in term of functionality?
0
2108
by: seanseaghan | last post by:
New to this group, so greetings all! I am trying to develop query syntax in Access 2000 to accomplish the following: Imagine you are in an accounting dept. and you are working on a reconiliation of debits and credits. Say you have a table / list that is 1000s of lines long. These could be either debits or credits. Then imagine you know that a certain
14
8093
by: Darin | last post by:
I have a table that I want to delete specific records from based on data in other tables. I'm more familiar with Access '97, but am now using 2003, but the database is in 2000 format. In '97, I think I could have easily done this using joins, but I kept getting "could not delete from specified tables" errors. Some google searching has indicated I need to use a subquery. After many failed attempts with different approaches, I finally...
13
2876
by: forbes | last post by:
Hi, I have a user that used the Query Wizard to create a query in Access. Now she claims that her master table is missing all the data that was excluded from the query. Can you create anything other than a select query using the Wizard? What do you think happened to her data? I am working remotely until Friday, so I can't get down to her office and check out what she did.
3
3818
by: Kevin M | last post by:
I have one table and have created a form from that table. Also, I have created a delete query. I want to but a button on the form which will delete all records from the table; however, I cannot get anything to work. I know this is probably simple for more experienced Access users. Any help would be greatly appreciated. Thanks Kevin
3
1583
by: rbukkara | last post by:
Hi guys, I have some trouble with the following query. Please look into this and lemme know the solution ASAP. It certainly involves aggregations and the 'having clause' BROKER( ID integer primary key , NAME string)
0
5712
debasisdas
by: debasisdas | last post by:
Using Subqueries ================== The sub query is often referred to as a nested SELECT, Sub - SELECT, or inner SELECT statement. The sub query executes once before the main query. The result of the sub query is used by the main query (outer Query). You can place the sub query in a number of SQL clauses. WHERE clause
0
9810
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9656
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10821
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10241
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9358
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
6973
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5642
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
5812
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
4001
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.