Update with inner join not working in db2

Question

I have a structure similar to a working query in mysql that is an update based on an inner join with counts

update schema.daily_totals ct
  inner JOIN (
    SELECT
        COUNT (*) AS contacted,
        SUM( CASE WHEN f.follow_up_date BETWEEN CURRENT_DATE AND CURRENT_DATE + 7 DAYS THEN 1 ELSE 0 END ) AS potentials,
        CAST (ROUND((SUM( CASE WHEN f.follow_up_date BETWEEN CURRENT_DATE AND CURRENT_DATE + 7 DAYS THEN 1.0 ELSE 0 END )/ COUNT (*)) * 100.00, 2) AS DECIMAL (12, 2)) AS PERCENT,
        u.user_id as userID,

    FROM schema.users u
      INNER JOIN schema.notated n
        ON n.user_identifier = u.user_id
      INNER JOIN schema.comms m
        ON n.comms_ID = m.comms_ID
      LEFT JOIN schema.FDates f
        ON f.dNumber = n.dNumber
    WHERE code <> 'none'
    AND n.created_at >= CURRENT_DATE - 1 DAYS
    GROUP BY u.user_id, u.first_name, u.last_name
  ) as cu

  on cu.userID = ct.ext_id
  set ct.contacted_contacted = cu.contacted,
  ct.percent_up_to_date = cu.percent
  where ct.date_of_report >= current_date;

But it won't run, it seems to break around the final 'on' where I'm joining on the subquery.

Am I not able to run this in db2 at all?

It wants to break up the query at the line ``` on cu.userID = ct.ext_id``` and not run the full thing, I'm wondering if I have a syntax issue possibly — Geoff_S, Jan 23 '19 at 06:50
Possible duplicate of [SQL update from one Table to another based on a ID match IN db2](https://stackoverflow.com/questions/23285136/sql-update-from-one-table-to-another-based-on-a-id-match-in-db2) — mustaccio, Jan 23 '19 at 13:33

score 2 · Accepted Answer · answered Jan 23 '19 at 07:38

Use MERGE statement instead.

MERGE INTO schema.daily_totals ct
  USING (
    SELECT
        COUNT (*) AS contacted,
        SUM( CASE WHEN f.follow_up_date BETWEEN CURRENT_DATE AND CURRENT_DATE + 7 DAYS THEN 1 ELSE 0 END ) AS potentials,
        CAST (ROUND((SUM( CASE WHEN f.follow_up_date BETWEEN CURRENT_DATE AND CURRENT_DATE + 7 DAYS THEN 1.0 ELSE 0 END )/ COUNT (*)) * 100.00, 2) AS DECIMAL (12, 2)) AS PERCENT,
        u.user_id as userID,

    FROM schema.users u
      INNER JOIN schema.notated n
        ON n.user_identifier = u.user_id
      INNER JOIN schema.comms m
        ON n.comms_ID = m.comms_ID
      LEFT JOIN schema.FDates f
        ON f.dNumber = n.dNumber
    WHERE code <> 'none'
    AND n.created_at >= CURRENT_DATE - 1 DAYS
    GROUP BY u.user_id, u.first_name, u.last_name
  ) as cu

  on cu.userID = ct.ext_id and ct.date_of_report >= current_date
  WHEN MATCHED THEN UPDATE
  set contacted_contacted = cu.contacted, percent_up_to_date = cu.percent;

Update with inner join not working in db2

1 Answers1

Linked