Could not deleted from specified tables once again (1 Viewer)

[jaryszek]

Hi,

I have query like:

SELECT DISTINCT Temp_VolumeChanges.Ordinal, qry_Environments.EnvironmentID, tblVolumeNames.VolumeNameID AS VolumeNameIDFK, tblTopologies.TopologyID, tblTiers.TierID, tblServers.ServerID, tblPhysicalDiskModels.PhysicalDiskModelID AS PhysicalDiskModelIDFK, Temp_VolumeChanges.NumberOfDisks AS DiskNumber, Temp_VolumeChanges.MountPath, Temp_VolumeChanges.FilesystemType, tblCustomers.CustomerID, tblCustomers.CustomerName, Temp_VolumeChanges.PercentSnapshotSpace, Temp_VolumeChanges.ShareMode, Temp_VolumeChanges.SubjectComponent, Temp_VolumeChanges.FormatCommand, Temp_VolumeChanges.Description, Temp_VolumeChanges.Function, Temp_VolumeChanges.Caching, Temp_VolumeChanges.writeAcceleratorEnabled
FROM tblTopologies RIGHT JOIN ((tblServers RIGHT JOIN (tblPhysicalDiskModels RIGHT JOIN (((Temp_VolumeChanges LEFT JOIN tblTiers ON Temp_VolumeChanges.Tier = tblTiers.TierName) LEFT JOIN qry_Environments ON Temp_VolumeChanges.Production = qry_Environments.Environment) LEFT JOIN tblVolumeNames ON Temp_VolumeChanges.VolumeName = tblVolumeNames.VolumeName) ON tblPhysicalDiskModels.PhysicalDiskModelName = Temp_VolumeChanges.PhysicalDiskModel) ON tblServers.Server = Temp_VolumeChanges.Server) LEFT JOIN tblCustomers ON Temp_VolumeChanges.Customer = tblCustomers.CustomerName) ON tblTopologies.TopologyName = Temp_VolumeChanges.Topology;

On this query i am adding all necessary IDs to combine tables from database and imported from Excel.

and i am trying to use delete query on this like:

DELETE DISTINCTROW qryIm_SourceVolumes.*, tblVolumes.Ordinal
FROM qryIm_SourceVolumes LEFT JOIN tblVolumes ON qryIm_SourceVolumes.Ordinal = tblVolumes.Ordinal
WHERE (((tblVolumes.Ordinal) Is Null));

but this is not working, i have error "Could not deleted from specified tables".
Select query within this sql is working like a charm,
why? How to do this?

Best,
Jacek

 

[Ranman256]

due to some joins, (and yours is a doozie) the query cannot delete.
the query is too complex to perform this.

simplify the query.
also you cannot run a query on delete like you can with a simple Select query.

Use the query wizard and build a FIND DUPLICATES query.
look at the design, notice the IN structure in the criteria.
You must create a query similar to this for a delete query.

 

[jaryszek]

thank you Ranman256,

you have right, maybe my query is to "advance".
Thank you i will try use sql with IN statement,

Best,
Jacek

 

[June7]

DELETE can only act on 1 table. Also, won't work with DISTINCTROW as that is a form of data aggregation. Reference table to delete from, not that query.

DELETE does not require listing fields because the entire record is removed.

Why are you linking on name fields instead of the primary key ID fields? Should be saving ID instead of names to data table.

 

[jaryszek]

thank you Guys,

i know where i have an error,

I will fix my sql.

Best,
Jacek

 

[isladogs]

DELETE can only act on 1 table. Also, won't work with DISTINCTROW as that is a form of data aggregation. Reference table to delete from, not that query.

Whilst I agree with most of this answer, DELETE DISTINCTROW is often necessary where there DELETE query references a second table e.g. where one of the tables does not contain a PK field. For example you are deleting old records from an Access table that do not exist in a CSV or Excel file containing the latest records

See my article on Synchronising Data - especially the second page

I agree with the point made by Ranman about identifying duplicates and using that to simplify the DELETE query though this is more difficult if any of the fields contain null values.

I'm in the process of writing a more detailed answer in Jaracek's parallel thread which covers much the same topic More than 15 left joins ...

 

[jaryszek]

Thank you Colin,

my name is Jacek i am curious what you will write there ;-) Yes null can be solved for example like here (i am using this with success):

SELECT DISTINCTROW t2.*
FROM qryIm_SourceVolumes AS t2 LEFT JOIN tblVolumes AS t1 ON 
(t2.PercentSnapshotSpace = t1.PercentSnapshotSpace or (t2.PercentSnapshotSpace is Null AND t1.PercentSnapshotSpace is Null))

Best,
Jacek