Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 01-08-2019, 12:47 AM   #1
jaryszek
Newly Registered User
 
Join Date: Aug 2016
Posts: 533
Thanks: 252
Thanked 0 Times in 0 Posts
jaryszek is on a distinguished road
Delete not existing row based on multiple columns SQL

Hi,

i want to delete all records in tblDescription which are not existing in ExcelTempTable and i want to add these ones existing in ExcelTempTable and not existing in tblDescription.

I tried with:

Quote:
delete DescriptionID from tblDescription as t1 left join ExcelTempTable as t2 on
t1.[eyecolor] = t2.[eyecolor] and
t1.[hairstyle] = t2.[hairstyle] and
t1.[noseshape] = t2.[noseshape]
Where t2
but i do not know which field should i use in "Where" statements.
Please help how to build it.



Best,
Jacek
Attached Images
File Type: png Screenshot_17.png (16.6 KB, 115 views)
Attached Files
File Type: accdb sample (3).accdb (568.0 KB, 23 views)

jaryszek is offline   Reply With Quote
Old 01-08-2019, 01:15 AM   #2
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 9,270
Thanks: 105
Thanked 2,490 Times in 2,286 Posts
isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all
Re: Delete not existing row based on multiple columns SQL

The correct syntax for the delete query would be
Code:
DELETE tblDescription.*, ExcelTempTable.EyeColor, ExcelTempTable.HairStyle, ExcelTempTable.NoseShape
FROM tblDescription LEFT JOIN ExcelTempTable ON (tblDescription.NoseShape = ExcelTempTable.NoseShape) AND (tblDescription.HairStyle = ExcelTempTable.HairStyle) AND (tblDescription.EyeColor = ExcelTempTable.EyeColor)
WHERE (((ExcelTempTable.EyeColor) Is Null) AND ((ExcelTempTable.HairStyle) Is Null) AND ((ExcelTempTable.NoseShape) Is Null));
I assume this is dummy data but in this case no records would be deleted.

Does the Excel table contain all required records?
If so I would just start again by emptying the Description table then appending all records from Excel
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
The Following User Says Thank You to isladogs For This Useful Post:
jaryszek (01-08-2019)
Old 01-08-2019, 01:16 AM   #3
Minty
AWF VIP
 
Minty's Avatar
 
Join Date: Jul 2013
Location: UK - Wiltshire
Posts: 6,268
Thanks: 148
Thanked 1,683 Times in 1,655 Posts
Minty is a jewel in the rough Minty is a jewel in the rough Minty is a jewel in the rough
Re: Delete not existing row based on multiple columns SQL

If the criteria is that they don't exist in T2 Then simply use

WHERE t2.ExcelID is Null

You can prove this in the query designer by using a select first to make sure you are getting the correct records.

But your syntax isn't correct - Colin has updated to the correct way.
Also the sample data doesn't help prove the issue.

__________________
If we have helped please add to our reputation - click the scales symbol on the left, tick 'I approve' and leave a comment.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Minty is offline   Reply With Quote
Old 01-08-2019, 01:20 AM   #4
jaryszek
Newly Registered User
 
Join Date: Aug 2016
Posts: 533
Thanks: 252
Thanked 0 Times in 0 Posts
jaryszek is on a distinguished road
Re: Delete not existing row based on multiple columns SQL

hi isladogs,

thank you.

Quote:
Does the Excel table contain all required records?
Yes, Excel data is always actual and contain all required records.

Quote:
If so I would just start again by emptying the Description table then appending all records from Excel
your sql is not working, i have "Could not delete from specific table".

Best,
Jacek
jaryszek is offline   Reply With Quote
Old 01-08-2019, 01:24 AM   #5
jaryszek
Newly Registered User
 
Join Date: Aug 2016
Posts: 533
Thanks: 252
Thanked 0 Times in 0 Posts
jaryszek is on a distinguished road
Re: Delete not existing row based on multiple columns SQL

Quote:
Originally Posted by Minty View Post
If the criteria is that they don't exist in T2 Then simply use

WHERE t2.ExcelID is Null
hi Minty,

thanks. Problem is that excelID is autonumber primary key from Access. In Excel i do not have unique ID.

I used :

Code:
DELETE tblDescription.*
FROM tblDescription AS t1
WHERE not exists (SELECT 1 from ExcelTempTable as t2 
              where t2.[eyecolor] = t1.[eyecolor] AND t2.[hairstyle] = t1.[hairstyle] AND t2.[noseshape] = t1.[noseshape]);
it is correct?

Best,
Jacek
jaryszek is offline   Reply With Quote
Old 01-08-2019, 01:38 AM   #6
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 9,270
Thanks: 105
Thanked 2,490 Times in 2,286 Posts
isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all
Re: Delete not existing row based on multiple columns SQL

Sorry. Forgot to specify unique records

Using Access verbose syntax:
Code:
DELETE DISTINCTROW tblDescription.*, ExcelTempTable.EyeColor, ExcelTempTable.HairStyle, ExcelTempTable.NoseShape
FROM tblDescription LEFT JOIN ExcelTempTable ON (tblDescription.NoseShape = ExcelTempTable.NoseShape) AND (tblDescription.HairStyle = ExcelTempTable.HairStyle) AND (tblDescription.EyeColor = ExcelTempTable.EyeColor)
WHERE (((ExcelTempTable.EyeColor) Is Null) AND ((ExcelTempTable.HairStyle) Is Null) AND ((ExcelTempTable.NoseShape) Is Null));
or more concisely
Code:
DELETE DISTINCTROW tblDescription.*
FROM tblDescription LEFT JOIN ExcelTempTable ON (tblDescription.NoseShape = ExcelTempTable.NoseShape) AND (tblDescription.HairStyle = ExcelTempTable.HairStyle) AND (tblDescription.EyeColor = ExcelTempTable.EyeColor)
WHERE (((ExcelTempTable.EyeColor) Is Null) AND ((ExcelTempTable.HairStyle) Is Null) AND ((ExcelTempTable.NoseShape) Is Null));
You could of course use table aliases to shorten that still further, but all you need to empty the table is

Code:
DELETE tblDescription.* FROM tblDescription;
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
The Following User Says Thank You to isladogs For This Useful Post:
jaryszek (01-08-2019)
Old 01-08-2019, 01:44 AM   #7
jaryszek
Newly Registered User
 
Join Date: Aug 2016
Posts: 533
Thanks: 252
Thanked 0 Times in 0 Posts
jaryszek is on a distinguished road
Re: Delete not existing row based on multiple columns SQL

wow, thank you.

why to use distinctrow and it is must have condition?

Quote:
DELETE tblDescription.* FROM tblDescription;
O nice, so after deleting you would use insert into?

Quote:
If so I would just start again by emptying the Description table then appending all records from Excel
your sentence is very helpful! Maybe i should use the solution for this topic also:

https://www.access-programmers.co.uk...d.php?t=303116

How all time deleting and inserting data will have impact on my database performance?

Best,
Jacek

jaryszek is offline   Reply With Quote
Old 01-08-2019, 07:09 AM   #8
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 13,792
Thanks: 76
Thanked 1,536 Times in 1,424 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Delete not existing row based on multiple columns SQL

Quote:
How all time deleting and inserting data will have impact on my database performance?
This process is sometimes called "churning" and has the potential to cause databases to become bloated. Bloated database have a lot of deleted records that for technical reasons cannot be removed from the data base. The records typically have little or no effect on database speed once the app has been launched and is running, but it can slow down (slightly) the speed with which the file is initially loaded.

The solution is to every so often run a Compact & Repair operation on the file where these tables are stored. The more often you churn your data, the more often you will need to run that C&R operation. It implies that your DB will need some "maintenance down time." If that is easily managed, then schedule a particular time and date for which your DB is not accessible.

Final note: As a safety precaution, always make a backup copy of any database for which you are about to do a structural operation, and the C&R operation is one such operation. That way, if something bad happens, you have the copy. And if nothing bad happens, that copy becomes your backup "recovery point" if a later recovery is required.
__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
The Following User Says Thank You to The_Doc_Man For This Useful Post:
jaryszek (01-09-2019)
Old 01-08-2019, 07:55 AM   #9
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 9,270
Thanks: 105
Thanked 2,490 Times in 2,286 Posts
isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all
Re: Delete not existing row based on multiple columns SQL

Jacek
Apologies for the delay in replying

I decided to create an article comparing various different methods of synchronising data between tables:
1. Update existing records / append new / delete old
2. Combined update & append (upend( / delete old
3. Delete all / append all
4. Make table & overwrite existing

See the attached PDF & example database
Another version of the attached article is available at my website

Doc has already discussed issues related to database bloat and the need for backups - both of which I've also covered in my article

You probably understand most of this already but your last answer indicated some of the information may be new

If the import table is an Excel or CSV file it may not have a unique ID field
Similarly it may not always be appropriate to use an ID field even if it exists (as in your example)
In such cases you MUST specify unique records = yes (DISTINCTROW) in your queries

See this link for more info on the UPEND (AKA UPSERT) query: http://www.mendipdatasystems.co.uk/u...ery/4594428616

HTH
Attached Files
File Type: zip SyncDataExample.zip (26.6 KB, 18 views)
File Type: zip Updating a table from external data.zip (286.2 KB, 19 views)
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Last edited by isladogs; 01-08-2019 at 10:40 AM. Reason: Added website link
isladogs is offline   Reply With Quote
The Following User Says Thank You to isladogs For This Useful Post:
jaryszek (01-09-2019)
Old 01-09-2019, 01:20 AM   #10
jaryszek
Newly Registered User
 
Join Date: Aug 2016
Posts: 533
Thanks: 252
Thanked 0 Times in 0 Posts
jaryszek is on a distinguished road
Re: Delete not existing row based on multiple columns SQL

hi Guys,

thank you.


Quote:
Similarly it may not always be appropriate to use an ID field even if it exists
do you know other examples?

From your website:

Quote:
Indexing the fields will make the process slower as the indexes also need to be updated.
all primary keys are indexed automatically by Access, yes?

From your article i know that for me the better option is to retain table and update/delete and append only differences, not whole table.
thanks for that!

I do not understand this for NoPK and SyncDataExample example:

Quote:
First update existing records – as no PK field is involved, you should set Unique Records = Yes
this query is looking for the same fields. So in your SyndDataExample inner join is combing the same values. If they are the same - there is nothing to update so why to do this?

Best,
Jacek
jaryszek is offline   Reply With Quote
Old 01-09-2019, 01:40 AM   #11
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 9,270
Thanks: 105
Thanked 2,490 Times in 2,286 Posts
isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all
Re: Delete not existing row based on multiple columns SQL

Hi Jacek

1. You can't use the ID field if the field values in the import table don't match the autonumber ID values in the destination table.

2. Yes PK fields are automatically indexed with no duplicates allowed
Indexing other fields significantly speeds up searches but slows down update queries
See http://www.mendipdatasystems.co.uk/s...sts/4594424200

3. Of course there is no point updating if the existing values are the same as those in the import.
But without checking every record in each table, how would you know without trying the update query?

You MIGHT want to do this if if the data has changed since it was first imported e.g. change of address / phone number..
Also if someone has changed the data after it was imported and if the import value is the correct one to use
To make the point clearer, I will modify my example data so the values aren't identical. Thanks for pointing that out
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Last edited by isladogs; 01-09-2019 at 01:57 AM.
isladogs is offline   Reply With Quote
Old 01-09-2019, 01:56 AM   #12
jaryszek
Newly Registered User
 
Join Date: Aug 2016
Posts: 533
Thanks: 252
Thanked 0 Times in 0 Posts
jaryszek is on a distinguished road
Re: Delete not existing row based on multiple columns SQL

hi,

like your site a lot of useful things there in plain english for people like me ;-)

Do you have maybe any article about indexing on your website ?

Quote:
You MIGHT want to do this if someone has changed the data after it was imported and if the import value is the correct one to use
I do not understand.

Assume that in tblData and tblImportNOPK we have the same fields values:
StartDate, EndDate, NumberField.

Somebody will change field NumberField in tblImportNOPK from 3 to 5.
So this record will be not included in update query because tblData .NumberField <> tblImportNOPK.NumberField.
So query will skip this value. what is the point here?

Best,
Jacek
jaryszek is offline   Reply With Quote
Old 01-09-2019, 02:01 AM   #13
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 9,270
Thanks: 105
Thanked 2,490 Times in 2,286 Posts
isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all
Re: Delete not existing row based on multiple columns SQL

Thanks
I haven't done an article about indexing but I'm sure you'll find something suitable online

I edited the earlier comment after I posted to make it clearer, See if it makes sense now

Good point about the update query - I will edit that part of the article
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
Old 01-09-2019, 05:20 AM   #14
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 9,270
Thanks: 105
Thanked 2,490 Times in 2,286 Posts
isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all
Re: Delete not existing row based on multiple columns SQL

Jacek
I've just updated my website article following your earlier comments
Many thanks for pointing out the mistake I had made

I've now amended method 5 - update query where there is no linking PK field.
I've also added comments about its limitations / usefulness

The attached files are the updated items on the website.
Attached Files
File Type: zip Synchronise a table with external data_v2.zip (301.1 KB, 11 views)
File Type: zip SyncDataExample _v2.zip (35.4 KB, 12 views)

__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Summing Multiple Columns based on same customer names bg18461 Queries 4 05-12-2009 12:40 PM
Format existing columns of data AbeA Tables 3 09-18-2007 08:58 AM
Auto add columns to existing table Vista2007 Forms 1 05-02-2007 10:11 AM
[SOLVED] adding columns to pre-existing databases? baumann_pat Tables 4 10-08-2005 09:45 AM
Delete query comparing multiple columns SunWuKung Queries 1 07-16-2004 03:38 PM




All times are GMT -8. The time now is 03:03 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World