Deleting Records based on selections from another table

[NBVC]

Hi,

I am trying to create a Delete query.

I am trying to delete a specific part from multiple BOMs in my database.

I have a table of the BOMs that I want to look in. I called this table PartTable. I also linked my database table SYSADM.REQUIREMENT which contains all the requirement parts for all of our BOMs.

So I am wanting to delete only part number 123XX from each of the BOMs in my PartTable.

I am able to select the records with:

SELECT SYSADM_REQUIREMENT.*
FROM SYSADM_REQUIREMENT INNER JOIN PartTable ON SYSADM_REQUIREMENT.WORKORDER_BASE_ID = PartTable.PART_ID
WHERE (((SYSADM_REQUIREMENT.WORKORDER_TYPE)="M") AND ((SYSADM_REQUIREMENT.WORKORDER_BASE_ID)=[PartTable].[PART_ID]) AND ((SYSADM_REQUIREMENT.WORKORDER_LOT_ID)="0") AND ((SYSADM_REQUIREMENT.PART_ID)="123XX"));

Now how do I delete these same records.

I am getting error saying I have to select a table to delete from....

 

[Alansidman]

In the QBE, have you changed your Select Query to a Delete Query? Is that where you are getting the error message?

 

[jdraw]

Further to Alan's comment, you should be aware that DELETE queries are quite unforgiving. If you don't select the exact records you want to delete, there is no simple UNDO command.

1.Backup your data.
2.Create a SELECT query to identify exactly which records you intend to delete.
3. Convert to a DELETE query

 

[NBVC]

The Select Query I have shown above is does select the records I want to delete. So I did try as you suggested Alan, by simply change the Select Query to a Delete Query, and that is where I get the error.

This is what the SQL looks like:

DELETE SYSADM_REQUIREMENT.*, SYSADM_REQUIREMENT.WORKORDER_TYPE, SYSADM_REQUIREMENT.WORKORDER_BASE_ID, SYSADM_REQUIREMENT.WORKORDER_LOT_ID, SYSADM_REQUIREMENT.PART_ID
FROM SYSADM_REQUIREMENT INNER JOIN PartTable ON SYSADM_REQUIREMENT.WORKORDER_BASE_ID = PartTable.PART_ID
WHERE (((SYSADM_REQUIREMENT.WORKORDER_TYPE)="M") AND ((SYSADM_REQUIREMENT.WORKORDER_BASE_ID)=[PartTable].[PART_ID]) AND ((SYSADM_REQUIREMENT.WORKORDER_LOT_ID)="0") AND ((SYSADM_REQUIREMENT.PART_ID)="123XX"));

It then tells me that it could not delete from specified tables.

P.S. I am testing this in my SANDBOX environment first. Thanks for re-iterating the importance of this though

 

[Brianwarnock]

I am no expert but I thought that if you were deleting from the parts table then that would be the one with the Tablename.* in the Delete

Brian

 

[NBVC]

Hi Brian,

I am trying to delete from the SYSADM.REQUIREMENT table. I only want to delete records that have a BOM (part ID) that is contained in my PartTable table.

 

[Brianwarnock]

Sorry, guess I misunderstood this.

So I am wanting to delete only part number 123XX from each of the BOMs in my PartTablem.

Then I haven't a clue what a BOM is

Brian

 

[NBVC]

It's a Bill of Material

So the SYSADM.REQUIREMENT is made up of all the child parts for every BOM in our system. One of the columns in this table, called WORKORDER_BASE_ID, contains the BOM number for the child parts. I am trying to reduce this list firstly to all the child parts of only the BOM numbers listed in my PartTable table. Once reduced to this list, I want to only segregate out part number 123XX records found in the PART_ID column.... and then delete those records only.

Hope it's a bit clearer.

 

[Brianwarnock]

I have had a little play, I think that you need to set your unique records property to yes

Right click on the * col in your design grid and on the local menu set the uniquerecords property to yes, if working in SQL view its DELETE DISTINCTROW etc

Brian

 

[NBVC]

Hey Brian,

That did the trick. Thanks a lot. Do you know why that is necessary here? The Select statement on it's own came up with the 4 records...

 

[Brianwarnock]

Not really, it's my first contact with delete queries but help said that it was needed in a two table type situation, I need to read more and try to get my head round it but I think it's because the database is opened as read only without it and I guess I could waffle on a bit more

Brian

 

[NBVC]

No worries, now that I know what fixed it, I will research it more finely...

Thanks again.