delete statement before insert statement

laknar · #1 (**permalink**) 06-11-09, 12:30

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.

Ax238 · #2 (**permalink**) 06-11-09, 12:51

Try:

Code:

delete from table1 where name='ABC';

insert into table1 (name) values('ABC');

Regards,

Ax

laknar · #3 (**permalink**) 06-11-09, 13:02

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.

laknar · #4 (**permalink**) 06-12-09, 05:15

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

sathyaram_s · #5 (**permalink**) 06-12-09, 06:51

When you say, "Not working" - is there a error message you can share ?

tonkuma · #6 (**permalink**) 06-12-09, 07:42

What is the reason deleting and inserting same value?
Is it not enough to do nothing, if the value exists?

laknar · #7 (**permalink**) 06-12-09, 08:03

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

tonkuma · #8 (**permalink**) 06-12-09, 08:59

lakner, you wrote:

Quote:

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?

laknar · #9 (**permalink**) 06-12-09, 09:15

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.

Cougar8000 · #10 (**permalink**) 06-12-09, 09:33

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?

sathyaram_s · #11 (**permalink**) 06-12-09, 09:50

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.

laknar · #12 (**permalink**) 06-12-09, 12:57

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.

stolze · #13 (**permalink**) 06-12-09, 21:34

Have a look at the MERGE statement.

laknar · #14 (**permalink**) 06-13-09, 01:10

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')

tonkuma · #15 (**permalink**) 06-13-09, 02:34

laknar, you wrote:

Quote:

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) 
;

Internet Services

Web Development

Digital Marketing

Consumer Tech