i have two tables
table1:
pnr, dmax
01, 2017-02-02 11:10:00
02, 2017-05-02 10:10:10
and table2:
pnr, type, loc, dt
01, c3, l2, 2017-02-02 11:10:00
01, c3, l2, 2017-01-01 09:00:00
01, c3, l3, 2017-01-01 07:54:30
02, c5, l1, 2017-02-05 01:10:00
02, c5, l2, 2017-03-01 19:00:10
02, c5, l3, 2017-05-02 10:10:10
i like to delete all rows in Table2 which pair is NOT found in Table1 but i'm stuck with my limited mysql knowledge
something like ..... where pnr = pnr AND dmax != dt
please help
DELETE table2
FROM table2
LEFT JOIN table1
ON table1.pnr = table2.pnr AND table1.dmax = table2.dt
WHERE table1.pnr IS NULL
The statement above uses a basic LEFT JOIN. This will return NULL in the right table where no records are found in the left and those are the records we want to delete so we add the IS NULL to the where clause
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments