query to delete a record

[homer.favenir]

i have a table1 in my msaccess and i linked another table in mysql to ms access, (table2).
when table1 matches the record in table2, it will delete the record in table1.

my query is
delete *
FROM Table1
where table1.artid = table2.artid;

but it always ask me the value of tbl2.artid, the value of tbl2.artid is in the database of mysql and it has already records...

how can i compare the records of table1 (msaccess) and table2 (mysql) and delete the duplicate records?


thanks in advance

 

[neileg]

Your SQL doesn't make sense. You need a join. I assumeyou are wanting to delete any record in table1 where the ID also exists in table2. The SQL would look like this

DELETE Table1.*
FROM Table1 INNER JOIN Table2 ON Table1.artID = Table2.artID;

 

[homer.favenir]

is it okey if the table2 is from mysql?

 

[teacherqasim]

Dear

R u Carazy that you are going to use two different database engine

 

[homer.favenir]

could not delete from table

delete table1.*
from
table1 inner join table2 ON table1.artid = table2.artid
where (((table1.artid) = table2.artid))

the error is:
could not delete from specified table.

i cant see my error here....

 

[Jon K]

Try using the keyword DistinctRow in the query. (There is no need to use a Where Clause.)

DELETE DistinctRow Table1.*
FROM Table1 INNER JOIN Table2 ON Table1.ArtID=Table2.ArtID;
.

 

[teacherqasim]

Query

Dear,
CAn u help me in solving a problem,

Problem​

I have a table with following fields
RollNo, Marks_English, Marks_Math, Marks_Urdu,Total_Obtained, Remarks
1. I want to design a query which calculate the total_obtained marks by added the marksof english, urdu and math and place themin field"Total_Obtained"
2. I want to design a query which fill Pass or fail in remarks if all the subjects contain above 40 marks in each subject.

Please reply soon

 

[Jon K]

See the query in the attached database.

Normally we don't store calculated values in a table (in your case Total_Obtained and Remarks.) We just do the calculations whenever the values are needed.

Your table looks more like an Excel spreadsheet than a database table. If you find it difficult to manipulate the marks further, you may consider normalizing the table structure. (Do a search on these forums.)
.

 

[homer.favenir]

Try using the keyword DistinctRow in the query. (There is no need to use a Where Clause.)

DELETE DistinctRow Table1.*
FROM Table1 INNER JOIN Table2 ON Table1.ArtID=Table2.ArtID;
.

hi,
i have a problem...
the query is
if table1.column1 = table2.column1 and table1.column2 = table2.column2,delete.
only column1 and column2 of both tables should match and delete the records.
the query above is for all columns.
correction for the query above?

thanks

 

[Jon K]

You can specify the joining condition in the On Clause.

DELETE DistinctRow Table1.*
FROM Table1 INNER JOIN Table2 ON table1.column1 = table2.column1 and table1.column2 = table2.column2
.

 

[homer.favenir]

156976 != 00156976

You can specify the joining condition in the On Clause.

DELETE DistinctRow Table1.*
FROM Table1 INNER JOIN Table2 ON table1.column1 = table2.column1 and table1.column2 = table2.column2
.

great!thanks!!!
anyway, i have a followup question. how about if both columns is different
e.g.
table1.column1 = 156976
and
table2.column1 = 00156976

how can i match this two columns? 156976 is different from 00156976
can i use wildcards so that all 156976 = 00156976

thanks a lot!....

 

[Jon K]

You can use the Val() function to strip the leading zeros from the numbers for comparison. But then you can't use the keyword DistinctRow with Val(). You have to use a subquery with IN, but a subquery can return only one field.

DELETE *
FROM Table1
WHERE Val(column1) IN (SELECT Val(table2.column1) FROM Table2);
.