Inner Join in Update SQL for Db2

UPDATE JOIN statement for DB2

Try this:

UPDATE table2
SET table2.PageID = 
    (SELECT t1.PageID
    FROM table1 t1
   WHERE t1.id = table2.SCRID)
WHERE EXISTS(
    SELECT 'TABLE1PAGE'
    FROM table1 t1
    WHERE t1.id = table2.SCRID)

I've added EXISTS clause to prevent NULL assignment to PageID of table2

How to update in db2 with join

There's just a simple syntax error. You have an excess semi colon before your where exists clause:

update semester_workload tA
set tA.account = (SELECT DISTINCT (t2.WORKING - t2.WORKLOAD) FROM SEMESTER_WORKLOAD t1
        join SEMESTER_WORKLOAD t2 on t1.ID_LECTURER = t2.ID_LECTURER
        WHERE t2.ACADEMIC_SEMESTER = 'WS10' AND tA.ID_SW = t2.ID_SW) -- ; remove this
where exists (SELECT 1 FROM SEMESTER_WORKLOAD t1        
        join SEMESTER_WORKLOAD t2 on t1.ID_LECTURER = t2.ID_LECTURER
        WHERE t2.ACADEMIC_SEMESTER = 'WS10' AND tA.ID_SW = t2.ID_SW);

JOIN in UPDATE sql for DB2

I solved the problem , just took out the join and did a inner select

Update gk.WR_VEHICLE_WARRANTY 
set CURRENT = '1'
Where FK_GARANT_FRIST_ZUWEIS in 
(select PK_GARANT_FRIST_ZUWEIS from gk.VGARANT_FRIST_ZUWEIS z
where z.GW = '1'
  and z.FK_GBE is null
  and z.INTERN = '0' ) 

IBM DB2 update using JOIN

Okay so this is really late but in case someones reading this: none of the comments/answers were correct. The important point is that i am working on an iseries which uses db2 udb and does neither support joins on updates nor merge (at least the version we work with).
The only way i figured out will work is a WHERE EXISTS clause.

Update in DB2 using where exists instead of join

DB2 for i does not allow the UPDATE table FROM that DB2LUW allows

You have two solutions

One is UPDATE using one subquery to select the rows to update, and another to get the value of main_discount

update main_library.details as main
set less_amount = ROUND(cast (price as float) * (
   (select main_discount from 2nd_library.discounts DISC
            where wholesale_num  = main.wholesale_num)
  /100),2)
    where exists(
        select 0 from 2nd_library.discounts DISC
            where wholesale_num  = main.wholesale_num
    )

the other is MERGE

MERGE INTO main_library.details main
using(
select wholesale_num, main_discount from 2nd_library.discounts DISC
) disc on disc.wholesale_num = main.wholesale_num
when matched then
  update set less_amount = ROUND(cast (price as float) * (main_discount)/100),2)

Maybe you should use DECFLOAT rather than FLOAT to avoid suprises

Update with inner join not working in db2

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;

---

Related Topics