Delete Records with conditions (1 Viewer)

zahranahmad70

New member
Local time
Today, 22:44
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

error reading drive A:
Local time
Tomorrow, 03:44
Joined
May 7, 2009
Messages
9,619
DELETE * FROM employee_certificates WHERE emp_no IN (SELECT emp_no FROM employee_master);
 

zahranahmad70

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

arnelgp

error reading drive A:
Local time
Tomorrow, 03:44
Joined
May 7, 2009
Messages
9,619
From which table are you deleting?
 

arnelgp

error reading drive A:
Local time
Tomorrow, 03:44
Joined
May 7, 2009
Messages
9,619
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
Local time
Today, 12:44
Joined
Oct 29, 2018
Messages
10,809
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

CID Moderator
Staff member
Local time
Today, 20:44
Joined
Jan 14, 2017
Messages
13,428
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:44
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 (Users: 0, Guests: 1)

Top Bottom