How do I update a subset of values in DB2 based on related tables?

Question

I am trying to remove the last 10 characters from a CLOB field in a DB2 database. I can do so using:

UPDATE  report
SET     comment = LEFT(comment, (LENGTH(comment) - 10))

However, I want to limit the truncation to a subset of the rows based on whether or not the report is in the current reporting period. I tried this...

UPDATE  report
SET     comment =
        (   SELECT  LEFT(comment, (LENGTH(comment) - 10))
            FROM    report

                    INNER JOIN report_period
                    ON report.report_period_id = report_period.report_period_id

            WHERE   report_period.name = '2013 Interim Report'  )

...but I get

The result of a scalar fullselect, SELECT INTO statement, or
VALUES INTO statement is more than one row

What am I doing wrong?

score 0 · Answer 1 · answered Apr 05 '13 at 00:37

Never mind... asking the question helped clear it up in my head. Just had to move the join to the WHERE clause....

UPDATE  report
SET     comment = LEFT(comment, (LENGTH(comment) - 10))
WHERE   report_id IN
        (   SELECT  report_id 
            FROM    report

                    INNER JOIN report_period
                    ON report.report_period_id = report_period.report_period_id

            WHERE   report_period.name = '2013 Interim Report'  )

How do I update a subset of values in DB2 based on related tables?

1 Answers1