Design an SQL that will delete the MIN value in one field

[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

[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.

[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