Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Jul 2008
    Posts
    94

    Unanswered: delete statement before insert statement

    hi,

    i have a delete statement before insert statement in Stored procedure.

    when i try to insert a value through loop.if a value existing then delete the value first and insert the same.

    delete from table1 where name='ABC'

    insert into table1 values('ABC')

    but it is not working.can anyone suggest me.

  2. #2
    Join Date
    May 2009
    Posts
    258
    Try:
    Code:
    delete from table1 where name='ABC';
    
    insert into table1 (name) values('ABC');
    Regards,

    Ax

  3. #3
    Join Date
    Jul 2008
    Posts
    94
    table1 contains a single column.hence no need to give column name explicitly.

    i tested without delete statement insert is working fine.
    if i have a record before insert in table1 name values('ABC')

    we need to delete and insert the same in table1.

    but i believe .it is deleting both the rows.even i mention the delete statement out of the loop.

  4. #4
    Join Date
    Jul 2008
    Posts
    94
    declare MYCUR cursor for stmt;
    ...
    set tablename='MYTABLE';
    ...
    set :sqlstmt = 'select .. from ' || tablename || 'for update';
    set :delstmt= 'delete from '|| tablename || 'where current of MYCUR';

    prepare stmt from sqlstmt;

    open MYCUR;
    ...
    fetch MYCUR into ...;
    ...
    execute delstmt;

    I WANT TO DELETE THE RECORD BEFORE INSERTING THE SAME RECORD.
    FIRST CHECK IF ENTRY IS AVAILABLE.
    THEN DELETE AND INSERT THE SAME
    Last edited by laknar; 06-12-09 at 06:20.

  5. #5
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    When you say, "Not working" - is there a error message you can share ?
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    What is the reason deleting and inserting same value?
    Is it not enough to do nothing, if the value exists?

  7. #7
    Join Date
    Jul 2008
    Posts
    94
    when we run for first time the data are populated with wrong value.

    name----'ABC'
    id--------10

    again i have to rerun to populate correct value.

    name----'ABC'
    id--------12

  8. #8
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    lakner, you wrote:
    table1 contains a single column.hence no need to give column name explicitly.

    i tested without delete statement insert is working fine.
    if i have a record before insert in table1 name values('ABC')

    we need to delete and insert the same in table1.
    Would you share DDL(create table statement) with us?

  9. #9
    Join Date
    Jul 2008
    Posts
    94
    create table table1(name varchar(50),id integer,DATE_COLUMN DATE);

    insert into table1 values('ABC',100,'2009-06-12');

    insert into table1 values('DEF',100,'2009-06-12');

    NAME COLUMN IS TABLE NAME IM FETCHING THE TABLE NAME FROM TABLE1 WITH OUT CHECKING THE DATE.

    PROCESSING THE TABLENAME IN LOOP

    PREPARING DYNAIC SQL TO CHECK THE CERTAIN VALUES COUNT E.G

    SELECT COUNT(1) FROM ABC;

    INSERTING INTO SAME TABLE1 ID AS NUMBER OF RECORDS AND DATE AS PASSING IN PARAMETER.

    BEFORE INSERT I PLACED DELETE STATEMENT TO DELETE THE EXISTING ROWS IN DATE PARAMETER VALUE

    DELETE FROM TABLE1 WHERE DATE_COLUMN=VDATE;

    AFTER EXECUTING I SHOULD HAVE 4 ENTRIES IN TABLE1 BUT IM GETTING ONLY 3 ROWS.
    CAN YOU PLEASE ADVICE ME?I BELIEVE WHEN SECOND RECORD PROCESS FIRST RECORD GETTING DELETED.

    I PLACED THE DELETE STMT OUT OF LOOP.
    GETTING RESULT ONLY 2 ROWS.

  10. #10
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    Quote Originally Posted by laknar
    when we run for first time the data are populated with wrong value.

    name----'ABC'
    id--------10

    again i have to rerun to populate correct value.

    name----'ABC'
    id--------12
    If you place the wrong value. Wouldn't it be logical to fix your process that creates data first?

    and then if you still need to do what you are doing wouldn't it be easier to just run an UPDATE statement vs running two (delete then insert) statements?
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  11. #11
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    I am not I understand the question ...

    I am assuming that you want to delete some records and insert new rows with a different value for the id column for the deleted rows.


    Code:
    drop table table1
    DB20000I  The SQL command completed successfully.
    
    create table table1(name varchar(50),id integer,DATE_COLUMN DATE)
    DB20000I  The SQL command completed successfully.
    
    insert into table1 values('ABC',100,'2009-06-12')
    DB20000I  The SQL command completed successfully.
    
    insert into table1 values('DEF',100,'2009-06-12')
    DB20000I  The SQL command completed successfully.
    
    select * from table1
    
    NAME                                               ID          DATE_COLUMN
    -------------------------------------------------- ----------- -----------
    ABC                                                        100 2009-06-12
    DEF                                                        100 2009-06-12
    
      2 record(s) selected.
    
    
    drop procedure p1
    DB20000I  The SQL command completed successfully.
    
    CREATE PROCEDURE P1
    MODIFIES SQL DATA
    BEGIN
    declare sqlstmt varchar(255) ;
    declare delstmt varchar(255);
    declare stmt varchar(255) ;
    declare v_name varchar(50) ;
    declare v_id int ;
    declare v_date_column date ;
    declare sqlcode int  ;
    
    declare MYCUR cursor for select name,id,date_column from table1  for update;
    
    open mycur ;
    fetch mycur into v_name,v_id,v_date_column ;
    while (sqlcode <> 100)  do
    
       delete from table1 where current of mycur ;
    
       insert into table1 values(v_name,v_id*100,current date) ;
    
        fetch mycur into v_name,v_id,v_date_column ;
    
    end while ;
    
    
    END
    
    DB20000I  The SQL command completed successfully.
    
    call p1()
    
      Return Status = 0
    
    
    commit
    DB20000I  The SQL command completed successfully.
    
    select * from table1
    
    NAME                                               ID          DATE_COLUMN
    -------------------------------------------------- ----------- -----------
    ABC                                                      10000 2009-06-12
    DEF                                                      10000 2009-06-12
    
      2 record(s) selected.
    I don't think this is necessarily the best approach, but, I am trying to do something similar to what you have tried.
    If you can explain what you want with a proper example , I am sure someone here will be able to help.
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  12. #12
    Join Date
    Jul 2008
    Posts
    94
    i have checked that if an entry is present for the (date and tablename)(instead of delete)
    then im updating
    else insert
    (or)
    we can delete one shot for the particular date

    which is best approach?
    or any performance problem

    if a schema and tablename is parameter dynamic sql is the only way or anyother method to do that.
    Last edited by laknar; 06-12-09 at 14:01.

  13. #13
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Have a look at the MERGE statement.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  14. #14
    Join Date
    Jul 2008
    Posts
    94
    MERGE utility is in "DB2 v8.1.1.32" version.

    YOU MEAN I HAVE TO DO A SELF JOIN LIKE BELOW UPDATE OR INSERT.

    MERGE INTO TABLE1 AS A USING
    (Select name,id,DATE_COLUMN from TABLE1 WHERE DATE_COLUMN='2009-06-13') AS B
    ON A.DATE_COLUMN=B.DATE_COLUMN
    WHEN MATCHED THEN
    UPDATE SET
    ID=55
    WHEN NOT MATCHED THEN
    INSERT (name,id,DATE_COLUMN)
    VALUES('ABC',55,'2009-06-13')
    Last edited by laknar; 06-13-09 at 02:28.

  15. #15
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    laknar, you wrote:
    i have checked that if an entry is present for the (date and tablename)(instead of delete)
    So, you should check tablename, too. Like this:
    Code:
    MERGE INTO TABLE1 AS A
    USING (VALUES ('ABC', 55, '2009-06-13') ) AS B(name, id, date_column)
    ON  A.DATE_COLUMN = B.DATE_COLUMN
    AND a.name        = b.name
    WHEN MATCHED THEN
    UPDATE 
       SET ID = b.id
    WHEN NOT MATCHED THEN 
    INSERT 
    VALUES (b.name, b.id, b.date_column) 
    ;

Posting Permissions

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