Delete records from a table based on records in another

[dosia]

I have a table InvPrice and Updated Pricing

Need to delete all records from InvPrice that Match UpdatedPricing

InvPrice.StockCode = UpdatedPricing.StockCode
InvPrice.PriceCode = UpdatedPricing.StockCode

I have tried something like this...

Dim dbs As DAO.Database, sql As String, rCount As Integer
Set dbs = CurrentDb
sql = "DELETE * dbo_InvPrice Inner Join (dbo_InvPrice Inner Join UpdatedPricing on dbo_InvPrice.StockCode = UpdatedPricing.StockCode ) ON on dbo_INvPrice.PriceCode = UpdatedPricing.PriceCode "
dbs.Execute sql, dbFailOnError

Thanks

 

[jdraw]

Annnd WHAT happened?
Error Message?
Did you back up your database before you ran a DELETE
query?

You said this

InvPrice.StockCode = UpdatedPricing.StockCode
InvPrice.PriceCode = UpdatedPricing.StockCode

I think you mean
InvPrice.StockCode = UpdatedPricing.StockCode
InvPrice.PriceCode = UpdatedPricing.PriceCode

 

[dosia]

Annnd WHAT happened?
Error Message?
Did you back up your database before you ran a DELETE
query?

You said this


I think you mean
InvPrice.StockCode = UpdatedPricing.StockCode
InvPrice.PriceCode = UpdatedPricing.PriceCode

Yes you are correct.
Error = 3075 Syntax Error Syntax Error / Missing Operator

 

[jdraw]

Back up your database.!!!!!!!!!!!!!!

Try this (untested)

sql = "DELETE * FROM dbo_InvPrice 
       Inner Join  UpdatedPricing on 
       dbo_InvPrice.StockCode = UpdatedPricing.StockCode and 
       dbo_INvPrice.PriceCode = UpdatedPricing.PriceCode "

 

[spikepl]

Yours is not a good way of working. When preparing a DELETE query, the first thing to do is to ensure that the right records get selected for deletion. You do it by making a SELECT query!

Once the SELECT query does what it was supposed to, then you can replace SELECT by DELETE.

FUrther, you are designing the query by hand (otherwise you'd not have the glaring syntax error) - that is a waste of time, when you have an excellent design tool at your disposal : the query designer. Use it to get the syntax and everything right, and then you know exactly what SQL your code is supposed to produce.

 

[dosia]

Back up your database.!!!!!!!!!!!!!!

Try this (untested)

sql = "DELETE * FROM dbo_InvPrice 
       Inner Join  UpdatedPricing on 
       dbo_InvPrice.StockCode = UpdatedPricing.StockCode and 
       dbo_INvPrice.PriceCode = UpdatedPricing.PriceCode "

Well another error,"specify table from which you want to delete the records.

I want to delete records from table dbo_InvPrice

This is what i have
Dim dbs As DAO.Database, sql As String, rCount As Integer
Set dbs = CurrentDb
sql = "DELETE * FROM dbo_InvPrice Inner Join UpdatedPricing on dbo_InvPrice.StockCode = UpdatedPricing.StockCode and dbo_INvPrice.PriceCode = UpdatedPricing.PriceCode "
dbs.Execute sql, dbFailOnError

 

[jdraw]

I agree totally with spike. DElete queries are very unforgiving - if you don't make sure you are deleting the exact records you want to delete. Screw it up and your table is @@!#.

If you look for examples of deleting records using a join between tables, you won't find many.

First work on a query to SELECT the records to be deleted ONCE YOU ARE SURE THESE ARE THE ONES.

Try this
"SELECT
dbo_InvPrice.StockCode
, UpdatedPricing.StockCode
, dbo_INvPrice.PriceCode
,UpdatedPricing.PriceCode
FROM
dbo_InvPrice Inner Join UpdatedPricing on
dbo_InvPrice.StockCode = UpdatedPricing.StockCode and
dbo_INvPrice.PriceCode = UpdatedPricing.PriceCode "

Untested ---- see what you get and post back

 

[dosia]

I agree totally with spike. DElete queries are very unforgiving - if you don't make sure you are deleting the exact records you want to delete. Screw it up and your table is @@!#.

If you look for examples of deleting records using a join between tables, you won't find many.

First work on a query to SELECT the records to be deleted ONCE YOU ARE SURE THESE ARE THE ONES.

Try this
"SELECT
dbo_InvPrice.StockCode
, UpdatedPricing.StockCode
, dbo_INvPrice.PriceCode
,UpdatedPricing.PriceCode
FROM
dbo_InvPrice Inner Join UpdatedPricing on
dbo_InvPrice.StockCode = UpdatedPricing.StockCode and
dbo_INvPrice.PriceCode = UpdatedPricing.PriceCode "

Untested ---- see what you get and post back

My table UpdatedPricing is already a select query, now I need to delete all records from dbo_InvMaster when the records have same StockCode and PriceCode.

Can you see the attachement?

 

[jdraw]

No attachment

But your tables and names are changing??
I can't hit a moving target.

What is this suppose to mean

My table UpdatedPricing is already a select query

Show me a jpg of your tables and relationships.
What exactly are the table structures.
Do you have referential integrity set in your relationships?
Give us all the relevant information.

 

[dosia]

No attachment

But your tables and names are changing??
I can't hit a moving target.

What is this suppose to mean


Show me a jpg of your tables and relationships.
What exactly are the table structures.
Do you have referential integrity set in your relationships?
Give us all the relevant information.

I need to delete records from table dbo_InvPrice which are equal to records in UpdatedPricing table

The two tables could be linked by primary keys stockcode and pricecode

I have uploaded two attachements. Thanks

 

[jdraw]

There are no attachments.
Go to the GO Advanced button below this window to browse for files and upload them.

 

[dosia]

please check for attachments

 

[jdraw]

Why are your PKs datatype Text?
Can you show jpg of all tables and relationships?
Do you have referential integrity set on via the relationships?
Do you have Cascade delete set for any relationships - more detail please.

 

[Solo712]

Back up your database.!!!!!!!!!!!!!!

Try this (untested)

sql = "DELETE * FROM dbo_InvPrice 
       Inner Join  UpdatedPricing on 
       dbo_InvPrice.StockCode = UpdatedPricing.StockCode and 
       dbo_INvPrice.PriceCode = UpdatedPricing.PriceCode "

This won't work but since you are attempting to delete from joined tables. We need to create a subquery here. The code below should work (with Spike's caveat to test the select statement first, eg via temp table)

sql= "DELETE FROM dbo_InvPrice WHERE StockCode IN (
                      SELECT dbo_InvPrice.StockCode FROM
                      dbo_InvPrice Inner Join UpdatedPricing on 
                      dbo_InvPrice.StockCode = UpdatedPricing.StockCode AND 
                      dbo_InvPrice.PriceCode = UpdatedPricing.PriceCode) "

Best,
J.

 

[dosia]

Solo, thanks for your help the only problem i have now is that My PriceCode could be upper or lower case. How can I make sure that it will only query records with same PriceCode with same Case?