Results 1 to 8 of 8
  1. #1
    Join Date
    Feb 2004
    Posts
    11

    Unanswered: cursors usage in pl/sql

    Hi all,
    I am new to pl/sql programming . I am here trying to update the data in table 'test' with the data in table 'test1". For each row from 'test1' table, before i update it to 'test' table, i want to check whether that record already exist in table 'test' or not . If it exists i dont want to update it, if not i want to update the record.

    here are my tables
    test
    ----------
    MGMTCENTER NUMBER ,
    BANKGROUP NUMBER


    test1
    -----------
    MGMTCENTER NUMBER ,
    BANKGROUP NUMBER

    and here is my curcors code which is giving me errors


    let me explain the code:
    1. I am creating two cursors to two tables
    2. then i am staring the tests1 loop
    and inside it i am opening the 'test 'table loop and seeing whether that
    record already exists or not

    ----------------------------------
    declare
    cursor rad is ( select MGMTCENTER from test);
    cursor rad1 is (select MGMTCENTER, BANKGROUP from test1);
    begin
    for rad1_val in rad1
    loop
    for rad_val in rad
    loop
    if(rad1_val.MGMTCENTER != rad_val.MGMTCENTER)
    then
    insert into test values (rad1_val.MGMTCENTER, rad1_val.BANKGROUP)
    end loop;
    end loop;
    end;

    --------
    I am sure there are some syntax errors too.... so please let me know if you can help....thanks in advance....uma

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >If it exists i dont want to update it, if not i want to update the record.
    How can you UPDATE a record that does NOT exist?
    You'd better (re)take a class in basic logic & Boolean Algebra.

  3. #3
    Join Date
    Feb 2004
    Posts
    11
    Originally posted by anacedent
    >If it exists i dont want to update it, if not i want to update the record.
    How can you UPDATE a record that does NOT exist?
    You'd better (re)take a class in basic logic & Boolean Algebra.
    Hi anadedent,
    I hope I was not clear in my explanation.
    What i really meant to say is that ,
    'TEST' ===== Historical data
    'TEST1"====== One Day's data
    So when i want to update the historical data daily, i want to make sure the record i am going to add should be new one, it should NOT be a DUPLICATE. I hope i am clear enough this time. Thanks for your time...uma

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >What i really meant to say is that ,
    >'TEST' ===== Historical data
    >'TEST1"====== One Day's data
    >So when i want to update the historical data daily,
    >i want to make sure the record i am going to add should be new one,
    >it should NOT be a DUPLICATE.
    >I hope i am clear enough this time. Thanks for your time...uma
    HUH?
    1) One does NOT "update" records, you update columns/fields in record.
    2) How can "today's" data already exist in the "history" table?
    3) If you have/use PK or UNIQUE constraints, duplicates are precluded
    4) go RTFM on the MERGE statement in SQL
    5) I suspect this can be done with only using a single SQL statement

  5. #5
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi Uma,

    Don't worry, we all have to start somewhere, a fact which most members on here haven't forgotten.

    You will have to check the exact syntax, but something along the lines of the following would achieve your desired results in one statement.

    insert into test (select mgmtcenter, bankgroup from test1 where (mgmtcenter, bankgroup) not in (select mgmtcenter, bankgroup from test))

    For someone who takes the mickey out of posters so much for not mentioning version numbers, I am surprised that the posted reference to MERGE doesn't point out it's a feature only available in 9 onwards. MERGE is the better bet for you though (if you're on >=9).

    The MERGE statement available in 9 was designed to achieve exactly what you require.

    You have not properly defined the PK in TEST, I suspect it's (MGMTCENTER, BANKGROUP), adjust the above code accordingly.

    Hth
    Bill
    Please don't email me directly with questions. I've probably just got home from the pub and cannot guarantee the sanity of my answers. In fact, I can't believe I actually made it home.

  6. #6
    Join Date
    Feb 2004
    Posts
    11

    Thanks...

    Originally posted by billm
    Hi Uma,

    Don't worry, we all have to start somewhere, a fact which most members on here haven't forgotten.

    You will have to check the exact syntax, but something along the lines of the following would achieve your desired results in one statement.

    insert into test (select mgmtcenter, bankgroup from test1 where (mgmtcenter, bankgroup) not in (select mgmtcenter, bankgroup from test))

    For someone who takes the mickey out of posters so much for not mentioning version numbers, I am surprised that the posted reference to MERGE doesn't point out it's a feature only available in 9 onwards. MERGE is the better bet for you though (if you're on >=9).

    The MERGE statement available in 9 was designed to achieve exactly what you require.

    You have not properly defined the PK in TEST, I suspect it's (MGMTCENTER, BANKGROUP), adjust the above code accordingly.

    Hth
    Bill
    Hi Bill,
    Thank you very much.... your query solved my problem.... I am also trying to solve this problem using merge... uma

  7. #7
    Join Date
    Oct 2003
    Posts
    87
    Originally posted by anacedent
    >What i really meant to say is that ,
    >'TEST' ===== Historical data
    >'TEST1"====== One Day's data
    >So when i want to update the historical data daily,
    >i want to make sure the record i am going to add should be new one,
    >it should NOT be a DUPLICATE.
    >I hope i am clear enough this time. Thanks for your time...uma
    HUH?
    1) One does NOT "update" records, you update columns/fields in record.
    2) How can "today's" data already exist in the "history" table?
    3) If you have/use PK or UNIQUE constraints, duplicates are precluded
    4) go RTFM on the MERGE statement in SQL
    5) I suspect this can be done with only using a single SQL statement
    Back off -- you were a n00bie once too!
    Oracle - DB2 - MS Access -

  8. #8
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Originally posted by anacedent
    HUH?
    1) One does NOT "update" records, you update columns/fields in record.
    Apart from being irritating nit-picking, that isn't even true. If you update the columns in a row, you are also updating the row. What are you on about here?

    And if we are being pedantic (and that seems to be your bag), tables have rows and columns, not records and fields.

Posting Permissions

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