Different ways updating a table with sub select (join table)

/* 1. Standard sub query */ 
UPDATE tbl_employees x
   SET department = (
       SELECT y.name
         FROM tbl_department y
        WHERE y.dept_id = x.dept_id);

/* 2. Sub query with exist clause */
UPDATE tbl_employees x
   SET department = (
       SELECT y.name
         FROM tbl_department 
        WHERE y.dept_id = x.dept_id)
 WHERE EXISTS (
       SELECT y.name
         FROM tbl_department y
        WHERE y.dept_id = x.dept_id);

/* 3. Updatable view (needs foreign key connection) */
UPDATE (
SELECT x.department AS old_val,
       y.name AS new_val
  FROM tbl_employees x, tbl_department y
 WHERE y.dept_id = x.dept_id)
   SET old_val = new_val;

/* 4. Update trough merge statement */
MERGE INTO tbl_employees x
 USING (SELECT name, dept_id
          FROM tbl_department y ) y
    ON (y.dept_id = x.dept_id)
WHEN matched THEN
UPDATE
   SET x.department = y.name;

Post Tags:

APEX examples