Delete Records with conditions (1 Viewer)

zahranahmad70

New member
Local time
Today, 22:33
Joined
Oct 19, 2019
Messages
3
i have 2 tables, i need to delete all records from one table where matching records of second table. please help

Table one Fields (Employee_Master): Emp_no, Certificate_issue_Date
Table two Fields (Employee_Certificates): Emp_no, Certificate_issue_Date

i wand to delete all records from table two with matching records of table one,
but i keep receiving "Could not delete from specific tables"

Please help
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 03:33
Joined
May 7, 2009
Messages
19,169
DELETE * FROM employee_certificates WHERE emp_no IN (SELECT emp_no FROM employee_master);
 

zahranahmad70

New member
Local time
Today, 22:33
Joined
Oct 19, 2019
Messages
3
Thank you arnelgp. the condition is emp_no and certificate issue date, they both should mach
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 03:33
Joined
May 7, 2009
Messages
19,169
From which table are you deleting?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 03:33
Joined
May 7, 2009
Messages
19,169
DELETE * FROM employee_certificates WHERE emp_no & certificate_issue_date IN
(SELECT emp_no & certificate_issue_date FROM employee_master);
 

theDBguy

I’m here to help
Staff member
Local time
Today, 12:33
Joined
Oct 29, 2018
Messages
21,358
i have 2 tables, i need to delete all records from one table where matching records of second table. please help

Table one Fields (Employee_Master): Emp_no, Certificate_issue_Date
Table two Fields (Employee_Certificates): Emp_no, Certificate_issue_Date

i wand to delete all records from table two with matching records of table one,
but i keep receiving "Could not delete from specific tables"

Please help
Hi. Welcome to AWF!

This may or may not work but maybe also try something like:

Code:
DELETE Table1.*
FROM Table1
INNER JOIN Table2
ON Table1.Emp_no=Table2.Emp_no
  AND Table1.Certificate_issue_Date=Table2.Certificate_issue_Date
 

isladogs

MVP / VIP
Local time
Today, 19:33
Joined
Jan 14, 2017
Messages
18,186
You may get an error message that you cannot delete from the specified table.
If so, set Unique Records =Yes in the query properties.
That's the same as using DELETE DISTINCTROW … in your SQL statement
 

zahranahmad70

New member
Local time
Today, 22:33
Joined
Oct 19, 2019
Messages
3
Thank you isladogs.. (Unique Record = yes) did the trick, it worked like magic :)
 

Users who are viewing this thread

Top Bottom