have to alter table and update the values in altered temporary table. Is it possible to solve this problem or should I fulfill temporary table using loop instead of INSERT INTO ttb_customers SELECT * FROM CUSTOMERS;?

But the problem is I need a procedure to run in a job. I have two tables, they are similar and on of them is expanded. I need to migrate data from one table to another expanded table and fulfill with values depending on other values.

Here I made the little example how it looks like.

  -- create trmporaty table
    CREATE GLOBAL TEMPORARY TABLE ttb_customers
    ON COMMIT DELETE ROWS
    AS SELECT * FROM customers WHERE 1=0;



CREATE OR REPLACE PROCEDURE ADD_MISSING_ROWS IS
BEGIN
    EXECUTE IMMEDIATE 'ALTER TABLE ttb_customers ADD(
        email users.email%type,
        ubi number(20)
    ) ON COMMIT DELETE ROWS';
END;

CREATE OR REPLACE PROCEDURE migrate_customers IS
    email_ USERS.EMAIL%type;
    ubi_ number(20) := 0;
BEGIN
    INSERT INTO ttb_customers SELECT * FROM CUSTOMERS;

    ADD_MISSING_ROWS;

    FOR i IN (SELECT * FROM ttb_customers) LOOP
        IF i.SALARY > 500 THEN
            email_ := CONCAT(i.NAME, '@lux.com');
            ubi_ := 0;
        ELSE
            email_ := CONCAT(i.NAME, '@basic.com');
            ubi_ := 100;
        END IF;

        -- this part does not work
        UPDATE ttb_customers SET email=email_, ubi=ubi_ WHERE ID=i.ID;
    
    END LOOP;
END;

CodePudding user response：

You can run it with out a procedure

CREATE TABLE ttb_customers (email varchar2(100), name varchar2(100), ubi int, salary int)

INSERT INTO ttb_customers VALUES('[email protected]','tom', -1, 499)

INSERT INTO ttb_customers VALUES('[email protected]','James', -1, 591)

UPDATE ttb_customers SET email=
CASE WHEN SALARY > 500  THEN CONCAT(NAME, '@lux.com')
ELSE CONCAT(NAME, '@basic.com') END 
, ubi= CASE WHEN SALARY > 500  THEN 0 ELSE 100 END

SELECT * FROM ttb_customers

EMAIL         | NAME  | UBI | SALARY
:------------ | :---- | --: | -----:
[email protected] | tom   | 100 |    499
[email protected] | James |   0 |    591

db<>fiddle here

CodePudding user response：

And why not do it all in just one SQL, like this where END_TABLE is your 'normal' final table, not a GTT:

Insert into end_table (....,email,ubi)
select t.*,
CASE WHEN SALARY > 500  THEN CONCAT(NAME, '@lux.com')
ELSE CONCAT(NAME, '@basic.com') END ,
 CASE WHEN SALARY > 500  THEN 0 ELSE 100 END 
from ttb_customers;

Do you really need a GTT?