Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2012
    Posts
    8

    Unanswered: DELETE using table join. HELP.

    Hello, i'm new to DB2. I'm trying to delete rows from a table based on a time stamp from another table. Both tables have a column with similar data. I sort of need to JOIN the tables but this is done differently in DB2.

    I've tried a number of things like this:
    DELETE FROM "test".recipient
    where recipient.campaign_id in
    (SELECT * FROM "test".campaign where END_TS > current date - 30 days) (returns 4702 rows)

    But I get "The number of elements on each side of a predicate operator does not match"

    Basically I want to delete all records from a table named RECIPIENT, based on a timestamp (END_TS) from another table, that are greater than 30 days old. Both tables share a column of data named CAMPAIGN_ID.

    Can someone help me please? thanks.

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Code:
    DELETE FROM "test".recipient
    where recipient.campaign_id in
    (SELECT x.campaign_id FROM "test".campaign as x where x.END_TS > current date - 30 days)
    Select * returns all columns, you are only comparing 1.

    Andy

  3. #3
    Join Date
    Sep 2012
    Posts
    8
    thanks for your quick reply. I really appreciate it. I changed things to:
    DELETE FROM "TEST".recipient
    where campaign_id in
    (SELECT x.campaign_id FROM "TEST".campaign as x where x.END_TS < current date - 1 days)

    IWAQ0003W SQL warnings were found
    SQLState=02000 No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table

    however when I run the SELECT sub-query it correctly returns 1 row. Any other ideas?

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    It is the DELETE that is returning the warning, not the select.

    Andy

  5. #5
    Join Date
    Sep 2012
    Posts
    8
    yes sorry. There was no campaign_id 64 in my test recipient table. I updated the table and re-ran the delete and it worked. 5 rows deleted. Now to try it on a much larger data set.

    Many thanks for your help Andy. I appreciate it.

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
  •