•
•
•
•
What is DaniWeb IT Discussion Community?
You're currently browsing the Techies' Lounge section within the Tech Talk category of DaniWeb, a massive community of 455,968 software developers, web developers, Internet marketers, and tech gurus who are all enthusiastic about making contacts, networking, and learning from each other. In fact, there are 3,746 IT professionals currently interacting right now! Registration is free, only takes a minute and lets you enjoy all of the interactive features of the site.
Please support our Techies' Lounge advertiser: Programming Forums
Views: 1304 | Replies: 2
 |
•
•
Join Date: Nov 2007
Posts: 2
Reputation: 
Rep Power: 0
Solved Threads: 0
Newbie Poster
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
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
•
•
Join Date: Nov 2007
Posts: 51
Reputation:
Rep Power: 1
Solved Threads: 9
Junior Poster in Training
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.
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.
Last edited by Nige Ridd : Nov 23rd, 2007 at 4:16 pm.
•
•
Join Date: Nov 2007
Posts: 2
Reputation:
Rep Power: 0
Solved Threads: 0
Newbie Poster
Thank you very much, Nige for your assistance. I am now back on track! Have a great day
•
•
•
•
Originally Posted by 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
|
•
•
•
•
•
•
•
•
DaniWeb Techies' Lounge Marketplace
•
•
•
•
Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
Linear Mode
- My SQL Help :( (MySQL)
- Save Image in SQL Database Image field (ASP.NET)
- WizardSteps-> SQL Insert -> Grab ID field (ASP.NET)
- How to give 2 Conditions in an SQL Query (VB.NET)
- Min function (MS SQL)
- Delete related record (VB.NET)
- SQL Delete/Update Error (ColdFusion)
- Variable URLs from SQL Table (ASP.NET)
- sql help (Oracle)
- How can i deledet and update from table ??????? (PHP)
Other Threads in the Techies' Lounge Forum
- Previous Thread: fifth week of school
- Next Thread: What do ya guys think about Dual monitors?
