Member Avatar for hunguyen

I am trying to design an SQL statement that will delete the MIN value(s) in one field.

I have three fields of interest:

Table: SUM
Fields: Parcel, Soil_Texture, Area

Ex. Parcel #1 is duplicated three times b/c there are three Soil Ttextures and three Areas associated with it:

i.e.

Parcel Soil_Texture Area
1 Loam 10
1 Silt 20
1 Clay 15

**Parcel=MALGISTAG, Soil_Texture=TEX_CSSC, Area=SumOfArea_CALC...I used simpler field names in my example.

I would like my result to delete two records and keep the Silt with Area=20. Keep in mind that there are thousands of Parcels with duplicate records.

Below is code that seems to be on the right path, but I get the error, "Specify the table containing the record you want to delete".
------------------------------------------------------------------------------------
DELETE *
FROM SUM
LEFT OUTER JOIN (SELECT MALGISTAG, MAX(SumOfAREA_CALC) AS MaxArea
FROM SUM
GROUP BY MALGISTAGl) AS MaxSubQuery ON SUM.MALGISTAG = MaxSubQuery.MALGISTAG AND SUM.SumOfAREA_CALC = MaxSubQuery.MaxArea
WHERE MaxSubQuery.MaxArea IS NULL

  • 2 Contributors
  • 2 Replies
  • 196 Views
  • 6 Days Discussion Span
  • Latest Post Latest Post by hunguyen

Dani AI

Generated

— the error and behavior you saw are typical when the DELETE syntax or dialect is slightly off. was right to flag two things: remove the * from a DELETE, and avoid a table name that looks like a built‑in function (use brackets or rename it).

If you are on Microsoft SQL Server, the cleanest, safest way is to mark rows to keep with a window function and delete the rest. Example (rename columns/table to match your schema):

WITH cte AS (
SELECT PA.*,
ROW_NUMBER() OVER (PARTITION BY PA.ParcelID ORDER BY PA.AreaAmt DESC) AS rn
FROM dbo.ParcelAreas PA
)
DELETE FROM cte
WHERE rn > 1;

This keeps the single row with the largest AreaAmt for each ParcelID and deletes the duplicates. To keep the smallest instead, change ORDER BY to ASC. If you want to preserve all tied maxima, use RANK() or DENSE_RANK() instead of ROW_NUMBER().

If you are using MS Access (the error message looks like Access), use a correlated subquery because Access does not support ROW_NUMBER():

DELETE FROM [ParcelAreas] AS P
WHERE P.AreaAmt < (
SELECT MAX(Q.AreaAmt) FROM [ParcelAreas] AS Q WHERE Q.ParcelID = P.ParcelID
);

That removes every row whose area is less than the group maximum (ties for max are preserved). If you need to remove ties too, you need a more complex method.

Safety and troubleshooting tips: always run the SELECT version first to preview rows; perform the delete inside a transaction so you can ROLLBACK if needed; backup the table; bracket or rename reserved names like SUM; check for foreign keys or triggers that could cascade.

Recommended Answers

All 2 Replies

Member Avatar for Nige Ridd

You should get rid of the * after the delete.
Also I'm only having a guess here as I can't try it. But is it worth renaming the table 'SUM' to something else as in some systems 'SUM' is a reserved word in SQL.

Member Avatar for hunguyen

Thank you very much, Nige for your assistance. I am now back on track! Have a great day:)

I am trying to design an SQL statement that will delete the MIN value(s) in one field.

I have three fields of interest:

Table: SUM
Fields: Parcel, Soil_Texture, Area

Ex. Parcel #1 is duplicated three times b/c there are three Soil Ttextures and three Areas associated with it:

i.e.

Parcel Soil_Texture Area
1 Loam 10
1 Silt 20
1 Clay 15

**Parcel=MALGISTAG, Soil_Texture=TEX_CSSC, Area=SumOfArea_CALC...I used simpler field names in my example.

I would like my result to delete two records and keep the Silt with Area=20. Keep in mind that there are thousands of Parcels with duplicate records.

Below is code that seems to be on the right path, but I get the error, "Specify the table containing the record you want to delete".
------------------------------------------------------------------------------------
DELETE *
FROM SUM
LEFT OUTER JOIN (SELECT MALGISTAG, MAX(SumOfAREA_CALC) AS MaxArea
FROM SUM
GROUP BY MALGISTAGl) AS MaxSubQuery ON SUM.MALGISTAG = MaxSubQuery.MALGISTAG AND SUM.SumOfAREA_CALC = MaxSubQuery.MaxArea
WHERE MaxSubQuery.MaxArea IS NULL

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.