sql syntax for delete query (1 Viewer)

jaryszek

Registered User.
Local time
Today, 05:48
Joined
Aug 25, 2016
Messages
756
Hi,

i am using delete query like:

Code:
DELETE distinctrow tblImages.*
FROM tblImages LEFT JOIN Temp_Images ON tblImages.DisplayName = Temp_Images.DisplayName;

this is working for one or few fields but when i have more than 10 fields joined due to access restrictions - the query can not be executed.

I am looking for another syntax, sql to delete unmatched records.
Maybe exists function?
Please help,
Best,
Jacek
 

Attachments

  • Database10.accdb
    424 KB · Views: 47

isladogs

MVP / VIP
Local time
Today, 12:48
Joined
Jan 14, 2017
Messages
18,186
Here's another method which may work for you without using joins

This will delete unmatched pairs based on DisplayName only
Code:
Delete tblImages.*
From tblImages
Where Exists( Select 1 From Temp_Images Where Temp_Images.DisplayName = tblImages.DisplayName ) = False

or to delete matched pairs
Code:
Delete tblImages.*
From tblImages
Where Exists( Select 1 From Temp_Images Where Temp_Images.DisplayName = tblImages.DisplayName ) = True

I've only used one field for matching purposes
Obviously this could get complex if you need to match all the fields

Bear in mind that nulls will again cause issues as you can't equate a null with anything - not even another null

I would also expect it would be much slower than the other approach if you had a lots of records.

Hopefully someone else will come up with some different ideas as well
 
Last edited:

jaryszek

Registered User.
Local time
Today, 05:48
Joined
Aug 25, 2016
Messages
756
hi thnak you i will test it!

Maybe to workaround null issues i can write something like :

Select 1 From Temp_Images Where Temp_Images.DisplayName = tblImages.DisplayName or (Temp_Images.DisplayName is null and tblImages.DisplayName is null)
?

Best,
Jacek
 

jaryszek

Registered User.
Local time
Today, 05:48
Joined
Aug 25, 2016
Messages
756
Hi,

i created query "DeleteQry" like here to avoid nulls:
Code:
SELECT DISTINCT t1.ImageIDPK, t1.DisplayName, t1.Topology, t1.Tier, t1.Platform, t1.OSDistro, t1.OSVersion, t1.Origin, t1.PlatformRepository, t1.ImageID, t1.Publisher, t1.Offer, t1.SKU, t1.Version, t1.Urn, t1.OS, t1.SystemNameFull, t1.Customer
FROM tblImages AS t1 INNER JOIN Temp_Images AS temp ON (t1.Tier = temp.Tier or (t1.Tier is Null AND temp.Tier is Null)) AND (t1.Topology = temp.Topology or (t1.Topology is Null AND temp.Topology is Null)) AND (t1.DisplayName = temp.DisplayName or (t1.DisplayName is Null AND temp.DisplayName is Null)) AND (t1.Topology = temp.Topology or (t1.Topology is Null AND temp.Topology is Null));

and now i am getting results which are common for both tables.
How can i delete these ones which are not common and exists only in Temp_Images?

Best,
Jacek
 

isladogs

MVP / VIP
Local time
Today, 12:48
Joined
Jan 14, 2017
Messages
18,186
Trying to work with NULLS isn't easy.
You may remember that I wrote the following in another thread

It may be safest to manage null values by first setting them to valid but unused values in each field of the import & destination fields For example, update null dates to 01/01/9999 and number fields to an unlikely value such as -1000000 You will need to do this for each field separately in the two tables. For example:

UPDATE tblData SET tblData.StartDate = #1/1/9999# WHERE (((tblData.StartDate) Is Null));

Then run the above queries.

After completion run a further update query to revert the modified values in the destination table back to null. Once again do this for each field separately.
For example:
UPDATE tblData SET tblData.StartDate = Null WHERE (((tblData.StartDate)=#1/1/9999#));

If that doesn't help, please post your database with both tables including some records that SHOULD be deleted together with your query
 

jaryszek

Registered User.
Local time
Today, 05:48
Joined
Aug 25, 2016
Messages
756
Hi isladogs,

thank you i handled it as nulls as well.

DELETE tblImages.*
FROM tblImages
WHERE tblImages.IMAGEIDPK not in (select * from SelectQuery);

SelectQuery:
Code:
SELECT DISTINCT t1.ImageIDPK, t1.DisplayName, t1.Topology, t1.Tier, t1.Platform, t1.OSDistro, t1.OSVersion, t1.Origin, t1.PlatformRepository, t1.ImageID, t1.Publisher, t1.Offer, t1.SKU, t1.Version, t1.Urn, t1.OS, t1.SystemNameFull, t1.Customer
FROM tblImages AS t1 INNER JOIN Temp_Images AS temp ON (t1.Customer = temp.Customer or (t1.Customer is Null AND temp.Customer is Null)) AND (t1.SystemNameFull = temp.SystemNameFull or (t1.SystemNameFull is Null AND temp.SystemNameFull is Null)) AND (t1.OS = temp.OS or (t1.OS is Null AND temp.OS is Null)) AND (t1.Urn = temp.Urn or (t1.Urn is Null AND temp.Urn is Null)) AND (t1.Version = temp.Version or (t1.Version is Null AND temp.Version is Null)) AND (t1.SKU = temp.SKU or (t1.SKU is Null AND temp.SKU is Null)) AND (t1.Offer = temp.Offer or (t1.Offer is Null AND temp.Offer is Null)) AND (t1.Publisher = temp.Publisher or (t1.Publisher is Null AND temp.Publisher is Null)) AND (t1.ImageID = temp.ImageID or (t1.ImageID is Null AND temp.ImageID is Null)) AND (t1.PlatformRepository = temp.PlatformRepository or (t1.PlatformRepository is Null AND temp.PlatformRepository is Null)) AND (t1.Origin = temp.Origin or (t1.Origin is Null AND temp.Origin is Null)) AND (t1.OSVersion = temp.OSVersion or (t1.OSVersion is Null AND temp.OSVersion is Null)) AND (t1.OSDistro = temp.OSDistro or (t1.OSDistro is Null AND temp.OSDistro is Null)) AND (t1.Platform = temp.Platform or (t1.Platform is Null AND temp.Platform is Null)) AND (t1.Tier = temp.Tier or (t1.Tier is Null AND temp.Tier is Null)) AND (t1.Topology = temp.Topology or (t1.Topology is Null AND temp.Topology is Null)) AND (t1.DisplayName = temp.DisplayName or (t1.DisplayName is Null AND temp.DisplayName is Null)) AND (t1.Topology = temp.Topology or (t1.Topology is Null AND temp.Topology is Null));

and now i wnat to apped to tblImages this rows which are existing in Temp_Images and not existing in tblImages not using left join but exists (how can i do this?), can i use my selectQuery?
 

isladogs

MVP / VIP
Local time
Today, 12:48
Joined
Jan 14, 2017
Messages
18,186
Have you tried it (on a backup)
The best way to find out is often trial and error
 

Users who are viewing this thread

Top Bottom