CODEWITHSUNDEEP
sqldb2
Rohan Kishibe
Rohan Kishibe

Reputation: 667

how to update multiple rows from subquery?

I'm now using DB2 and try to update multiple rows, but below query returns SQL0811N, that says

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

I'm looking for the query which can update multiple rows by each values selected by sub-query. 

update
    table_004
set
    status = 99,
    bar_date = (
        select
            max(foo_date) as bar_date
        from
            table_001 t1
            inner join
                table_002 t2
            on  t1.keyA = t2.keyA
            inner join
                table_003 t3
            on  t2.keyA = t3.keyA
            inner join
                table_004 t4
            on  t4.keyB = t3.keyB
        where
            t1.id in(1, 2, 3, 4, 5)
        group by
            t1.id,
            t4.keyB
    )

Upvotes: 0

Views: 2589

Answers (2)

Esperento57
Esperento57

Reputation: 17492

Your table 004 is not necessary into the subsellect. Try this:

update table_004 t4
set (t4.status, t4.bar_date) =
(
    select  99,  max(foo_date)
    from table_001 t1
    inner join table_002 t2 on  t1.keyA = t2.keyA
    inner join table_003 t3 on  t2.keyA = t3.keyA
    where t1.id in(1, 2, 3, 4, 5) and t4.keyB = t3.keyB
    group by t1.id
)
where exists
(
    select *
    from table_001 t1
    inner join table_002 t2 on  t1.keyA = t2.keyA
    inner join table_003 t3 on  t2.keyA = t3.keyA
    where t1.id in(1, 2, 3, 4, 5) and t4.keyB = t3.keyB
)

Upvotes: 1

Rohan Kishibe
Rohan Kishibe

Reputation: 667

I have added

  • a conditions to compare id between the table which I want to update and the table which is used in subquery
  • where clause to update statement

and query is been executed exactly what I want. Hooray!

edited SQL:

update
    table_004 t0
set
    status = 99,
    bar_date = (
        select
            max(foo_date) AS bar_date
        from
            table_001 t1
            inner join
                table_002 t2
            on  t1.keyA = t2.keyA
            inner join
                table_003 t3
            on  t2.keyA = t3.keyA
            inner join
                table_004 t4
            on  t4.keyB = t3.keyB
        where
            t1.id in(1, 2, 3, 4, 5)
            and
            t0.keyB = t4.keyB -- added
        group by
            t1.id,
            t4.keyB
    )
    where t0.keyB in (100,101,102,103) -- added

Upvotes: 0

Related Questions