sscotti
asked on
Delete SQL intersection
I have a MySQL database that I want to delete some rows in a table from based upon whether or not a column in the table that I want to delete rows from has a defined value in the cross referenced table.
To be precise, one table has this format:
Account Table:
accnt_id
accnt_email varchar(50)
.....
accnt_date
and the other has this format.
Property Table:
prty_id
cty_id
accnt_id
prty_first_property
......
prty_date
Both tables have a column for accnt_id. I want to delete all rows from the Account table that do not have a row in the property table with accnt_id = accnt_id. Basically, if they don't have any properties with their account id in the properties table I want to delete that row in the accounts table.
SELECT * from `property` WHERE `accnt_id` = xx pulls all rows with accnt_id == xx from the propery table. I need to know how to use that through an intersection to delete matching rows in the accounts table.
A general reference for command line SQL manipulations would be appreciated.
To be precise, one table has this format:
Account Table:
accnt_id
accnt_email varchar(50)
.....
accnt_date
and the other has this format.
Property Table:
prty_id
cty_id
accnt_id
prty_first_property
......
prty_date
Both tables have a column for accnt_id. I want to delete all rows from the Account table that do not have a row in the property table with accnt_id = accnt_id. Basically, if they don't have any properties with their account id in the properties table I want to delete that row in the accounts table.
SELECT * from `property` WHERE `accnt_id` = xx pulls all rows with accnt_id == xx from the propery table. I need to know how to use that through an intersection to delete matching rows in the accounts table.
A general reference for command line SQL manipulations would be appreciated.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.