Results 1 to 9 of 9
  1. #1
    Join Date
    Oct 2005
    Posts
    5

    Unanswered: duplicating lines in table

    Hello

    In a table with columns A, B, C, D, E, F, I want to duplicate all lines
    where A='FOO', changing the name to 'BAR', and keeping all other
    column values the same.

    Could someone help me with the SQL for this ?

    Thanks

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    insert into table (a, b, c, d, e, f)
    (select 'BAR', b, c, d, e, f
    from table
    where a = 'FOO'
    );

  3. #3
    Join Date
    Oct 2005
    Location
    pune(india)
    Posts
    24

    duplication of lines in a table

    you can try this. write a trigger first.


    create trigger triggername
    after update of A on tablename
    for each row db2sql
    begin atomic
    insert into tablename values(select * from tablename where A = 'bar');
    end;


    now update only those rows that have 'foo'.
    just check out whether it works?

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    No, it won't work ... not in Oracle database, actually. It might compile on DB2 (if "for each row db2sql" has anything to do with DB2 database).

  5. #5
    Join Date
    Oct 2005
    Location
    pune(india)
    Posts
    24

    thatz true

    yeah,definitely it work only for DB2.

    but you can write corresponding trigger in oracle.
    Thanks

  6. #6
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    There is NO need for any trigger, use littlefoot's solution.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  7. #7
    Join Date
    Dec 2003
    Location
    Oklahoma, USA
    Posts
    354
    If you are just trying to select the data:
    Code:
    SELECT decode( A, 'FOO', 'BAR', A ), B, C, D, E, F
    FROM my_table;
    JoeB
    save disk space, use smaller fonts

  8. #8
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by sinwar
    yeah,definitely it work only for DB2.
    Actually, it won't work in DB2 either: you've got the syntax wrong.

  9. #9
    Join Date
    Oct 2005
    Posts
    5
    Code:
    insert into table (a, b, c, d, e, f)
    (select 'BAR', b, c, d, e, f
    from table
    where a = 'FOO'
    );
    That's what I needed for my program, I knew it must exist but
    i didn't know the syntax. Thank you for your help.

Posting Permissions

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