Solved

Delete SQL intersection

Posted on 2008-10-24
1
1,142 Views
Last Modified: 2012-05-05
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.
0
Comment
Question by:sscotti
1 Comment
 
LVL 9

Accepted Solution

by:
jamesgu earned 350 total points
ID: 22801398
delete from Account  
where not exists ( select 1 from Property where Property.accnt_id =  Account.accnt_id)

0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
This guide whil teach how to setup live replication (database mirroring) on 2 servers for backup or other purposes. In our example situation we have this network schema (see atachment). We need to replicate EVERY executed SQL query on server 1 to…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question