delete records based on query with multiple joins

[mrsbean]

I always have trouble when I need to create a query which will not be stored in Access and call on that query from another query. Today, I need to delete records in a table based on criteria in a table. Here is the query which does not work:

Delete   specData.sdID 
 from specData INNER JOIN ((SELECT specData.sdID
FROM (specMaster INNER JOIN specColumn ON specMaster.smID = specColumn.colsmID) INNER JOIN specData ON specColumn.columnID = specData.scID) as Q1) ON specData.sdID = Q1.sdID

In plain english, I want to delete records from specData if they are related to specMaster.smID = 1. specMaster is not directly related to specData. There is a table which sits in the middle between them, specColumn.

Thank advance for your help

MrsBean

 

[rac2]

Delete   specData.sdID

FROM specData
INNER JOIN (
  (SELECT specData.sdID 
   FROM specMaster 
   INNER JOIN specColumn 
           ON specMaster.smID = specColumn.colsmID 
   INNER JOIN specData 
           ON specColumn.columnID = specData.scID
  ) as Q1
) ON specData.sdID = Q1.sdID

To clarify.
A "specification" is represented by a row in specMaster.
The data for the specification is stored in several rows in specData, one piece of data per row.

The meaning of the data value in a specData row is given by the columnID. Like a name/value pair.

A columnID is associated with one "specification" by the colsmID. The association is made in the specColumn table. The colsmID points to a row in specMaster.

You wish to delete all of the data for "specification" number 1, specMaster.smID = 1.

BTW, the syntax for a DELETE statement is

DELETE table_name
FROM joined_tables
WHERE conditions_in_the_joined_tables

So

 Delete   specData.sdID

should be

 Delete   specData

Maybe

Delete   specData

FROM specData
INNER JOIN specColumn 
        ON specColumn.columnID = specData.scID

WHERE specColumn.colsmID = 1

The WHERE clause limits the damage to the columns which define specification No. 1. All of the rows for these columns will be deleted from specData.

WARNING! Make a copy of your table before testing this statement. Rows cannot be restored once they are deleted.

 

[mrsbean]

I know we're getting closer, but I'm not there yet. I took what you gave me, and the Q1 query in the middle was somehow muddled ... I inserted a fresh copy of my Q1 query, and I tried the statement with and without the sdid. The message I get from Access is "Specify the table containing the records you want to delete". Here is the code I used:

DELETE specData.sdid
FROM specData INNER JOIN [SELECT specData.sdID
FROM (specColumn INNER JOIN specMaster ON specColumn.colsmID = specMaster.smID) INNER JOIN specData ON specColumn.columnID = specData.scID
  ]. AS Q1 ON specData.sdID = Q1.sdID;

I will probably have to take out the square brackets (replace with parenthesis) for my Cold Fusion code when I get this straightened out.

MrsBean

 

[lespaul]

I don't think the syntax is quite correct...you can't just delete a single field....the delete statement deletes the entire record.

maybe:

DELETE specData  FROM specData INNER JOIN [SELECT specData.sdID
FROM (specColumn INNER JOIN specMaster ON specColumn.colsmID = specMaster.smID) INNER JOIN specData ON specColumn.columnID = specData.scID
  ]. AS Q1 ON specData.sdID = Q1.sdID;

(notice I removed sdid)
will delete all the records from the table specData that meet your criteria.

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[mrsbean]

Here is the code that worked:

DELETE specData.*
FROM specData
WHERE specData.sdID IN (SELECT specData.sdID
FROM (specMaster INNER JOIN specColumn ON specMaster.smID = 
specColumn.colsmID) INNER JOIN specData ON specColumn.columnID = 
specData.scID
WHERE specMaster.smID = 1);

I was having trouble saying Delete the records that match this criteria ... The In (select ...) got the job done.

MrsBean