Midrange News for the IBM i Community


Posted by: Viking
Information Systems
CA
Delete a record in embedded SQL
has no ratings.
Published: 16 May 2013
Revised: 20 May 2013 - 3987 days ago
Last viewed on: 16 Apr 2024 (5380 views) 

Using IBM i? Need to create Excel, CSV, HTML, JSON, PDF, SPOOL reports? Learn more about the fastest and least expensive tool for the job: SQL iQuery.

Delete a record in embedded SQL Published by: Viking on 16 May 2013 view comments(10)

The following SQL statement gets me the record I'm interested in. I want to delete that record. How do I properly combine this SELECT statement with the DELETE statement? Does this become the WHERE clause after DELETE FROM MYLIB/MYFILE, or a subselect, or?

SELECT MIN( DATE('20' ||
           trim(CHAR(YY)) || '-' ||
           trim(CHAR(MM)) || '-' ||
           trim(CHAR(DD)))) 
from MYLIB/MYFILE
where PERIOD = '22'

Thanks.

Return to midrangenews.com home page.
Sort Ascend | Descend

COMMENTS

(Sign in to Post a Comment)
Posted by: Viking
Premium member *
CA
Comment on: Delete a record in embedded SQL
Posted: 10 years 11 months 4 days 14 hours 2 minutes ago

It was WHERE EXISTS that I needed to tie them together.

Posted by: SteveCCNJ
Premium member *
Egg Harbor Twp., NJ
Comment on: Delete a record in embedded SQL
Posted: 10 years 11 months 4 days 11 hours 40 minutes ago
Edited: Thu, 16 May, 2013 at 15:30:05 (3991 days ago)

This would work as well:

DELETE FROM MYLIB/MYFILE
WHERE  PERIOD = '22'
AND    DateField = (
       SELECT MIN( DATE('20' ||
              trim(CHAR(YY)) || '-' ||
              trim(CHAR(MM)) || '-' ||
              trim(CHAR(DD)))) 
       FROM  MYLIB/MYFILE 
       WHERE PERIOD = '22'
       )

 

Posted by: Viking
Premium member *
CA
Comment on: Delete a record in embedded SQL
Posted: 10 years 11 months 3 days 15 hours 3 minutes ago

SteveCCNJ, what is DateField?  I don't have a specific date in my program, I just want to delete the oldest record in the file (based on YY, MM, DD) that has PERIOD = 22.

Posted by: SteveCCNJ
Premium member *
Egg Harbor Twp., NJ
Comment on: Delete a record in embedded SQL
Posted: 10 years 11 months 3 days 14 hours 30 minutes ago

Ah - Well then you would have to do your date construct in the place of DateField.

DELETE FROM MYLIB/MYFILE
WHERE  PERIOD = '22'
AND   (DATE('20' ||
     trim(CHAR(YY)) || '-' ||
     trim(CHAR(MM)) || '-' ||
     trim(CHAR(DD))))  = ( 
       SELECT MIN( DATE('20' ||
              trim(CHAR(YY)) || '-' ||
              trim(CHAR(MM)) || '-' ||
              trim(CHAR(DD)))) 
       FROM  MYLIB/MYFILE 
       WHERE PERIOD = '22'
       )
 
I was really just trying to show another way of doing this sort of thing.  This technique matches the value of a record in hand to the results of a subselect.
Posted by: bobcozzi
Site Admin ****
Chicagoland
www.cozTools.com/qryfile
Comment on: Delete a record in embedded SQL
Posted: 10 years 11 months 3 days 12 hours 50 minutes ago
Edited: Fri, 17 May, 2013 at 13:57:30 (3990 days ago)

Dale,

You may want to avoid your Cut/Paste option that seems to be inserting garbage into your Comments. Is it Rich Text or from RDi or what?

I fixed your first post for you.

I also noticed that the Syntax Highlighter is no longer rendering correctly. This seems like it was spontaneous, so it may be an external JS issue.

Posted by: Paulster
Premium member *
Sweden and The Netherlands
Comment on: Delete a record in embedded SQL
Posted: 10 years 11 months 1 days 2 hours 52 minutes ago

Sorry Bob, code still looks like RPG 6. Very modular I must say!

Posted by: DaleB
Premium member *
Reading, PA
Comment on: Delete a record in embedded SQL
Posted: 10 years 11 months 20 hours 17 minutes ago

Bob: Is there another Dale? I had started a reply in this thread, but never actually posted it. But Steve's post is a mess - could be the rendering problem to which you refer.

Viking: I the date unique in your table? Is it really three separate fields, and how are they declared?

Posted by: Viking
Premium member *
CA
Comment on: Delete a record in embedded SQL
Posted: 10 years 11 months 13 hours 23 minutes ago

The individual date fields YY, MM, and DD are all numeric (2,0).  The dates should be unique and a date shouldn't appear in more than one period.  PERIOD is (for some reason) 2A.

Here's what I've finally ended up with:

 

           Exec sql
             delete from MYFILE a
               where PERIOD = :inPeriod AND
                     digits(YY) || digits(MM) || digits(DD) =
                       (select min(digits(YY) || digits(MM) ||
                         digits(DD))
                          from MYFILE b
                          where b.PERIOD = a.PERIOD);

 

Posted by: SteveCCNJ
Premium member *
Egg Harbor Twp., NJ
Comment on: Delete a record in embedded SQL
Posted: 10 years 11 months 11 hours 47 minutes ago

Wow - let's try again, pulling the code right out of a text editor.

DELETE FROM MYLIB/MYFILE
WHERE  PERIOD = '22'
AND    DATE('20' ||
       trim(CHAR(YY)) || '-' ||
       trim(CHAR(MM)) || '-' ||
       trim(CHAR(DD)))  = 

      (SELECT MIN( DATE('20' ||
       trim(CHAR(YY)) || '-' ||
       trim(CHAR(MM)) || '-' ||
       trim(CHAR(DD)))) 
       FROM  MYLIB/MYFILE 
       WHERE PERIOD = '22'
       )

Posted by: Viking
Premium member *
CA
Comment on: Delete a record in embedded SQL
Posted: 10 years 11 months 8 hours 22 minutes ago

FYI:  After initially posting this question, I have learned that DIGITS is better to use for this than TRIM(CHAR( because DIGITS doesn't remove leading zeros like CHAR does.