CODEWITHSUNDEEP
sqlrpgle
nicowi
nicowi

Reputation: 472

RPG Embedded SQL delete several records with a list

How can I delete specific records from a table? I have a list with objects which I have to delete.

I've tried to save these names in a variable withh seperators and compare the key column with these variable.

PGNALIST = '(''PGM1'',''PGM2'',''PGM3'',...)'; 

EXEC SQL DELETE FROM FILE WHERE FILEPGNA IN :PGNALIST;

But it only works if I have a variable with just ONE program name.

Do know how I can solve these problem?

Also need these for SQL updates... The goal ist to use embedded Sql further.

Thanks a lot

Upvotes: 0

Views: 748

Answers (2)

Charles
Charles

Reputation: 23803

Can't do it like that...

For static SQL, you'd have to have multiple variables:

EXEC SQL 
  DELETE FROM FILE 
  WHERE FILEPGNA IN (:p1, :p2, :p3, ...);

Of course you need to know how many, or at least the maximum number of values you'll need to pass in.

Alternatively, you can use dynamic SQL to build the statement at runtime..

   dcl-c QUOTE '''';
   dcl-s myStmt varchar(1000);

   myStmt = 'delete from file where filepgna in ('
              + QUOTE + 'PGM1' + QUOTE
              + QUOTE + 'PGM2' + QUOTE
              + QUOTE + 'PGM3' + QUOTE 
              + ')';
   exec sql
    execute immediate :myStmt;

note that the QUOTE constant just makes life a little easier.

Upvotes: 2

Player1st
Player1st

Reputation: 1605

Charles is correct in that you can't do this directly. Frequently these types of problems arise when you haven't gone far enough with SQL. A typical SQL solution would pull the delete list from another table doing something like below:

EXEC SQL 
    DELETE FROM FILE 
    WHERE FILEPGNA IN (
        SELECT KEY FROM OTHERTABLE WHERE KEY LIKE 'PGM%');

If you are already pulling PGNALIST from another table, this is a lot more efficient and a lot less code.

Upvotes: 2

Related Questions