Update multiple columns using for all

Question

I am trying to update columns using for all. The set part of the update statement is constructed and then passed to the for all statement. There can be multiple columns in the set part.But it throws error : "missing equal sign". Any other method to solve this?

DECLARE
    V_COL_LIST VARCHAR2(4000);
    type emp_t
    IS
    TABLE OF NUMBER;
    emp_id emp_t;
BEGIN
    SELECT employee_id bulk collect
    INTO emp_id
    FROM employees
    WHERE department_id=10;

    V_COL_LIST:='SALARY=EMPLOYEE_ID';

    FORALL INDX IN 1..emp_id.count
    UPDATE EMPLOYEES SET V_COL_LIST
    WHERE EMPLOYEE_ID=emp_id(indx);
END;

Answer 1

The syntax you used is not recognized. You can use dynamic SQL to do this. However, I don't see why you are setting salary to employee_id in SALARY=EMPLOYEE_ID. Please check your logic.

Using dynamic SQL:

DECLARE
    V_COL_LIST VARCHAR2(4000);
    type emp_t
    IS
    TABLE OF NUMBER;
    emp_id emp_t;
BEGIN
    SELECT employee_id bulk collect
    INTO emp_id
    FROM employees
    WHERE department_id=10;

    V_COL_LIST:='SALARY=1000';

    FORALL INDX IN 1..emp_id.count
      execute immediate 'UPDATE EMPLOYEES SET ' || V_COL_LIST
    ||' WHERE EMPLOYEE_ID=:1' using emp_id(indx);
END;
/

If you know which columns are to be set to what, just you plain update-

DECLARE
    type emp_t
    IS
    TABLE OF NUMBER;
    emp_id emp_t;
BEGIN
    SELECT employee_id bulk collect
    INTO emp_id
    FROM employees
    WHERE department_id=10;

    FORALL INDX IN 1..emp_id.count
      UPDATE EMPLOYEES SET COL1 = 'ABC', COL2 = 'DEF'
       WHERE EMPLOYEE_ID = emp_id(indx);
END;
/