Delete not existing row based on multiple columns SQL (1 Viewer)

jaryszek · Jan 8, 2019

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:

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

isladogs · Jan 8, 2019

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

Minty · Jan 8, 2019

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.

jaryszek · Jan 8, 2019

hi isladogs,

thank you.

Does the Excel table contain all required records?

Yes, Excel data is always actual and contain all required records.

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 · Jan 8, 2019

Minty said:
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

isladogs · Jan 8, 2019

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;

jaryszek · Jan 8, 2019

wow, thank you.

why to use distinctrow and it is must have condition?

DELETE tblDescription.* FROM tblDescription;

O nice, so after deleting you would use insert into?

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/forums/showthread.php?t=303116

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

Best,
Jacek

The_Doc_Man · Jan 8, 2019

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.

isladogs · Jan 8, 2019

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/upend-query/4594428616

HTH

jaryszek · Jan 9, 2019

hi Guys,

thank you.

Similarly it may not always be appropriate to use an ID field even if it exists

do you know other examples?

From your website:

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:

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

isladogs · Jan 9, 2019

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/speed-comparison-tests/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

jaryszek · Jan 9, 2019

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 ?

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

isladogs · Jan 9, 2019

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

isladogs · Jan 9, 2019

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.

Delete not existing row based on multiple columns SQL (1 Viewer)

jaryszek

Registered User.

Attachments

isladogs

MVP / VIP

Minty

AWF VIP

jaryszek

Registered User.

jaryszek

Registered User.

isladogs

MVP / VIP

jaryszek

Registered User.

The_Doc_Man

Immoderate Moderator

isladogs

MVP / VIP

Attachments

jaryszek

Registered User.

isladogs

MVP / VIP

jaryszek

Registered User.

isladogs

MVP / VIP

isladogs

MVP / VIP

Attachments

Similar threads

Users who are viewing this thread