Results 1 to 7 of 7
  1. #1
    Join Date
    Jun 2009
    Posts
    4

    Unanswered: Delete query with join

    HI,

    I am new to db2.

    select a.* from table_A a, Table_b b where b.column1 in (case when a.column1 = '' then substr(column2,1,1) else a.column1 end) and a.column3 = b.column3 and b.column4 >= a.column4 and a.column5 = ' ' and a.column6 <> b.column6 and b.column7 > '2010-09-01'

    I want to create a delete query for the above select statement. I mean what ever rows extracted from the table_a using the above query needs to be deleted. Also I want to delete only 1000 rows.

    can anyone help me to create a delete query. Thanks in advance

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    I think something like this would work:

    Code:
    delete from tab_a where tab_a.primary_key in 
      (select primary_key from tab_a as a
       where exists (select 1 from tab_b as b 
            where b.column1 in (case when a.column1 = '' then substr(column2,1,1) else a.column1 end) and a.column3 = b.column3 and b.column4 >= a.column4 and a.column5 = ' ' and a.column6 <> b.column6 and b.column7 > '2010-09-01' )
       fetch first 1000 rows only)
    Andy

  3. #3
    Join Date
    Jun 2009
    Posts
    4
    Hi Andy,

    Thanks for ur reply!!

    The table_a doesn't have the primary key.
    Its having composite primary key.. column1+column2+column3 is the key in that table.

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    That is why I wrote the query the way I did.

    If Column1,column2,column3 constitutes a unique value, then just substitute "column1,column2,column3" for "primary_key".

    Andy

  5. #5
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Exclamation Than easier than better

    This one is looking good enough:

    Code:
    Delete from tab_a  A
    Where a.column5 = ' ' and
    exists 
    (select 1 from tab_b B where 
    b.column1 in 
    (case when a.column1 = ' ' 
    then substr(column2,1,1) 
    else a.column1 
    end) 
    and a.column3 = b.column3 
    and b.column4 >= a.column4  
    and a.column6 <> b.column6 
    and b.column7 > '2010-09-01' )
    Lenny

  6. #6
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Lenny,
    That will delete all the records that match, not just the first 1000.

    Andy

  7. #7
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963
    Quote Originally Posted by ARWinner View Post
    Lenny,
    That will delete all the records that match, not just the first 1000.

    Andy
    In program you can use the cursor to delete....

    Also you can use function ROW_NUMBER OVER() and delete only columns with numbers from 1 to 1000 within this query....

    Lenny
    Last edited by Lenny77; 09-29-10 at 12:26.

Tags for this Thread

Posting Permissions

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