Post reply

MS ACCESS Delete records exist on the other table with condition

  • October 15, 2010 at 8:11 am

    #100743

    I have table ZAC

    ZAC

    EDArrivalDateEDArrivalTimePNOZACMRNO

    201007210918100001694ZAC|9|20100721|0918||5350186

    ZAW

    PNOAdmSource

    100013732EO

    100013833EP

    I will like to delete all the records from ZAC where PNO is in ZAW and AdmSource in ZAW as EO or ES or EP

    I used the below sentence to delete . But I do not know what it delete all the record in the ZAC.

    strU="DELETE FROM ZAC WHERE EXISTS (SELECT PNO FROM ZAW WHERE

    ( (ZAW.AdmSource='EO') Or (ZAW.AdmSource='ES') Or (ZAW.AdmSource='EP') )and ZAC.PNO = ZAW.PNO );"

    CurrentDb.Execute strU

    Thanks.

    never mind, I found the solution.

  • January 1, 2011 at 11:43 am

    #1268241

    You have a problem with the way referential integrity between the two tables is organized. If you have one-to-one relationship than you'll by deleting one raw in table A, you'll as well delete one row in the table B. But if you have one-to-many relationship by deleting one raw in the table A you'll delete many rows in a table B. So just check how referential integrity is set up. As will, I think there is an option where you can have no rows in table B deleted, when raw in table A gets removed. That way you'll get lots of orphaned rows in table B, but that might be what you want.

    green smoke reviews

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply