Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2012
    Posts
    33

    Unanswered: Delete recordS from table ( more - to - more )

    Hello

    I have relationship more-to-more

    PERSON - PERSON_ADDRESS - ADDRESS

    PERSON

    -ID_PERSON
    -NAME
    -SURNAME

    PERSON_ADDRESS
    -ID_PERSON
    -ID_ADDRESS

    ADDRESS
    -ID_ADDRESS
    -NR_LOCAL
    -NR_HOME
    -PLACE

    I would like delete records with ADDRESS table when ID_PERSON = '01'. My query it:

    "DELETE FROM ADDRESS WHERE ADDRESS.ID_ADDRESS = PERSON_ADDRESS.ID_ADDRESS AND PERSON_ADDRESS.ID_PERSON = '01'"

    This is ok query?

    Thanks for advanced!

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >This is ok query?
    NO, invalid syntax

    what results from SQL below?
    SELECT COUNT(*) FROM PERSON_ADDRESS WHERE ID_PERSON = '01'?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Aug 2012
    Posts
    33
    SELECT COUNT(*) FROM person_address JOIN address ON person_address.id_address=address.id_address AND person_address.id_person='01'

    Result: 3



    I construed query:

    delete from ( select id_adres from adres where adres.id_adres IN ( select id_adres from osoba_adres where osoba_adres.id_person=1))

    And show error:

    SQL Error: ORA-02292: integrity constraint (SYSTEM.FK_ADRESKK) violated - child record found
    02292. 00000 - "integrity constraint (%s.%s) violated - child record found"
    *Cause: attempted to delete a parent key value that had a foreign
    dependency.
    *Action: delete dependencies first then parent or disable constraint.

    -- OK WORKING, MY SOLVE:


    delete from osoba_adres where id_person = 1;
    delete from adres where id_adres IN ( select id_adres from osoba_adres where adres.id_adres <> osoba_adres.id_adres)


    I great
    Last edited by dexter90; 09-22-12 at 07:07.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Code:
    DELETE FROM address 
    WHERE  address.id_address IN (SELECT person_address.id_address 
                                  FROM   person_address 
                                  WHERE  person_address.id_person = '01')
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •