Query using DELETE

Ask question on - DataBase Concepts, IBM Mainframe DB2, DB2/UDB, DB2 Tools, SQL , DB2 programming with languages like COBOL, PL1.

Moderators: Kalicharan, Moderator Group

Post Reply
hparthas
Member
Posts: 43
Joined: Thu Oct 21, 2010 11:17 pm

Query using DELETE

Post by hparthas » Sat Feb 05, 2011 9:44 pm

Hi,

I need to update the database based on the list of Product details.

My Table is defined as below

Filed Name : PRD_ID_DESC
Length : 50

The field comprises of Product ID (LENGTH - 6 CHAR) , some spaces, and Product desc.

I have the list of Product ID (LENGTH - 6 CHAR) and I need to delete from table.

Please find my DELETE Query:

DELETE FROM TABLE.PROD_TBLE
WHERE PRD_ID_DESC LIKE
('%481032%' , '%409038%' ) ;
COMMIT ;

BUT my SQL query is failing with -104 error.

Kindly advise me in the right direction.

Many Thanks in advance !!!!

User avatar
Krishna
Site Admin
Posts: 1052
Joined: Fri Jan 27, 2006 7:50 am

SQLCODE -104, LIKE, IN keyword

Post by Krishna » Sun Feb 06, 2011 7:55 pm

Instead of LIKE keyword use IN keyword.

hparthas
Member
Posts: 43
Joined: Thu Oct 21, 2010 11:17 pm

Post by hparthas » Sun Feb 06, 2011 10:00 pm

Thanks Krishna !!!!

I have changed LIKE to IN and the database have not been updated.

Instead I tried to SELECT the database with IN and the query resultant is '0' rows, but the table has records.

If I perform the same with single PRD_ID_DESC and 2 rows been fetched from my resultant query.

Please assist me in this !!!!

User avatar
Krishna
Site Admin
Posts: 1052
Joined: Fri Jan 27, 2006 7:50 am

Post by Krishna » Mon Feb 07, 2011 12:33 pm

Can you provide the sql statement that worked out.

hparthas
Member
Posts: 43
Joined: Thu Oct 21, 2010 11:17 pm

Post by hparthas » Mon Feb 07, 2011 9:52 pm

Krishna please find the SQL query.

I tried to SELECT the database to check whether any qualified rows are available in the DB, and can see few rows fetched. When I changed SELECT to DELETE the SQL is throwing +100 SQL error.

Delete Query :

Code: Select all

DELETE FROM TABLE.PROD_TBLE
WHERE PRD_ID_DESC LIKE
('%481032%' , '%409038%' ) ; 
SQL Code = +100

Select Query :
--------------

Code: Select all

SELECT * FROM TABLE.PROD_TBLE
WHERE PRD_ID_DESC LIKE
('%481032%' ) ; 
SQL Code = 2 Rows.

Kindly help me in this !!!!

User avatar
Krishna
Site Admin
Posts: 1052
Joined: Fri Jan 27, 2006 7:50 am

Post by Krishna » Tue Feb 08, 2011 2:49 pm

Following code might work.

Code: Select all

DELETE FROM TABLE.PROD_TBLE 
WHERE PRD_ID_DESC IN
('481032' , '409038' ) ;
when you are using IN keyword, symbol % may not work.

User avatar
dbzTHEdinosauer
Moderator
Posts: 981
Joined: Mon Oct 02, 2006 8:31 pm

Post by dbzTHEdinosauer » Tue Feb 08, 2011 2:56 pm

you may have more luck with:

Code: Select all

DELETE FROM table
WHERE POSSTR('481032',PRD_ID_DESC) > 0
   OR POSSTR('409038',PRD_ID_DESC) > 0
and the sql could easily be built with a REXX macro, when you have many, many PRD_ID_DESC

and Krishna is correct, the substitution symbols are not valid for IN-list contents.
Dick Brenholtz
JCL, SQL and code in programs have an irritating habit of doing what you say,
not what you meant.

Post Reply

FREE TUTORIALS

Tutorials
Free tutorials from mainframegurukul
  • JCL Tutorial
    Covers all important JCL concepts.
  • Cobol Tutorial
    This tutorials covers all Cobol Topics from STRING to COMP-3.
  • DB2 Tutorial
    DB2 Tutorial focuses on DB2 COBOL Programming.
  • SORT Tutorial
    This Tutorial covers all important aspects of DFSORT with examples
  • CICS Tutorial
    This CICS tutorial covers CICS concepts and CICS Basics, CICS COBOL Programming.
Interview
Mainframe Interview questions



Other References
Mainframe Tools and others