Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2005
    Location
    Waukesha WI
    Posts
    78

    Unanswered: Delete from table 1 based on table 2

    I have two tables:
    c_850 which contains fields C_ID, A_ID and G_ID
    miscm which contains fields CR_ID, AC_ID and GP_ID

    I wish to delete all records in c_850 where the fields match (C_ID = CR_ID, A_ID=AC_ID and GP_ID=G_ID)

    Seems pretty simple.
    But I keep getting an error with the following:

    delete from c_850
    where (miscm.cr_ID = c_850.C_ID
    AND miscm.AC_ID = c_850.A_ID
    AND miscm.GP_ID = c_850.G_ID);


    The error (904) occurs on the last line miscm.GP_ID and tells me that the miscm.gp_id is not a valid identifier.

    If I eliminate the line and try running it, it tells me that the miscm.AC_ID is not a valid identifier.

    delete from c_850
    where (miscm.cr_ID = c_850.C_ID
    AND miscm.AC_ID = c_850.A_ID);



    I know the field names are right, I must be doing something else wrong.

    What's the best way to handle this type of deletion?

  2. #2
    Join Date
    Jan 2005
    Location
    Green Bay
    Posts
    201
    With an exists

    delete from table a
    where exists in
    (select 'X' from
    table2 b
    where a.key1 = b.key1
    and a.key2 = b.key2);

  3. #3
    Join Date
    Jun 2005
    Location
    Waukesha WI
    Posts
    78

    Red face

    It's almost there.
    But I am getting some conflicting results when I do a select for a few individual values.

    I did a :

    insert into testtable
    (carrier_id, account_id, group_id)
    select carrier_id, account_id, group_id from c850 a
    where exists
    (select car_ID , acct_id, gp_id from
    mipscm b
    where a.carrier_id = b.car_ID
    and a.account_id = b.acct_id
    and a.GROUP_ID = b.GP_ID);

    and received most of the results.
    The fields in all three tables are the same:
    varchar2 20,15,15 respectively

    One of the accounts that I have isolated as not showing up is preceeded by zeros. In my older days of VB and Access, when things like this would happen it would most likely be from not comparing apples to apples. So I would use a TRIM() function.

    Anything come to mind that I could do here that would get those other ones?

  4. #4
    Join Date
    Jun 2005
    Location
    Waukesha WI
    Posts
    78

    Red face

    It's almost there.
    But I am getting some conflicting results when I do a select for a few individual values.

    I did a :

    insert into testtable
    (carrier_id, account_id, group_id)
    select carrier_id, account_id, group_id from c850 a
    where exists
    (select car_ID , acct_id, gp_id from
    mipscm b
    where a.carrier_id = b.car_ID
    and a.account_id = b.acct_id
    and a.GROUP_ID = b.GP_ID);

    and received most of the results.
    The fields in all three tables are the same:
    varchar2 20,15,15 respectively

    One of the accounts that I have isolated as not showing up is preceeded by zeros. In my older days of VB and Access, when things like this would happen it would most likely be from not comparing apples to apples. So I would use a TRIM() function.

    Anything come to mind that I could do here that would get those other ones?

  5. #5
    Join Date
    Jun 2005
    Location
    Waukesha WI
    Posts
    78

    Smile

    Sorry for the duplicate post.
    I solved the problem!!!

    It did require a TRIM().

    I usually do that as a rule so I don't run into problems but was unsure of doing it inside an Oracle SQL statement.

    Turns out it works like a champ!

    Thanks for the help!!

    Jerry

Posting Permissions

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