SQL INSERT, UPDATE and DELETE based on source table

Question

I have a source table with fields ID NAME and DESIG

create or replace table emp_source
(
    id number, 
    name string,
    desig string
);

And my destination table has columns ID NAME DESIG META_CRT_DT (date when record is inserted) META_UPD_DT (date when record is updated) META_IUD_FLG (to be updated based on Insert, Delete or Update operation performed) CURRENT_FLAG (if a record is deleted, I must make this false)

create or replace table emp_des
(
    id number, 
    name string, 
    desig string,
    META_CRT_DT timestamp_ntz,
    META_UPD_DT timestamp_ntz,
    META_IUD_FLG char,
    current_flag boolean
);

I'm inserting new record like this

INSERT INTO emp_des (id, name, desig,META_CRT_DT, 
                     meta_upd_dt, meta_iud_flg, current_flag)
SELECT
    id, name, desig, 
    to_date(current_timestamp) as crt,   
    to_date(current_timestamp) as upd, 
    'I' as iud, TRUE as flag 
FROM
    emp_source 
WHERE
    NOT EXISTS (SELECT * FROM emp_des 
                WHERE emp_source.id = emp_des.id);

When a record is updated in source table, say the designation is changed, in the EMP_DES table I need to update the DESIG, META_UPD_DT and META_IUD_FLG as 'U'.

Similarly when a record is deleted I need to update the META_UPD_DT and META_IUD_FLG as 'D'.

I'm new to SQL, so any help is appreciated. Thank you.

When a record is updated in source table, say the designation is changed, in the EMP_DES table I need to update the DESIG, META_UPD_DT and META_IUD_FLG as 'U'. I tried this but it changes all flags to 'U'.

UPDATE emp_des 
SET desig = s.desig, 
    meta_upd_dt = to_date(current_timestamp), 
    meta_iud_flg = 'U', 
    current_flag = 'TRUE' 
FROM emp_source AS s 
WHERE EXISTS (SELECT * FROM emp_des WHERE id = emp_des.id);

Similarly when a record is deleted I need to update the META_UPD_DT and META_IUD_FLG as 'D'.

I'm new to SQL, so I'd like to make it as simple as possible. Thank you for taking your time.

Your written description is not clear. Can you post the CREATE TABLE statements for these tables? — Tangentially Perpendicular, May 06 '23 at 02:18
Welcome to SO. Kudos for posting your schema. Now, when you stated SQL tag, does it refer to any form of MySQL, SQL Server, Oracle, etc etc? (e.g. Google Cloud SQL is a database-as-a-service (DBaaS) with the capability and functionality of MySQL) so what about yours? — bonCodigo, May 06 '23 at 04:18

score 1 · Answer 1 · answered May 06 '23 at 04:20

You are asked to maintain data about a source table in another table. The most "atomic" method for this would be to have triggers on the source table that automatically get triggered by certain events.

e.g. For an insert in the source table:

CREATE OR REPLACE TRIGGER emp_source_insert
AFTER INSERT ON emp_source
FOR EACH ROW
BEGIN
    INSERT INTO emp_des (id, name, desig, META_CRT_DT, meta_upd_dt, meta_iud_flg, current_flag)
    VALUES (:NEW.id, :NEW.name, :NEW.desig, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, 'I', TRUE);
END;

For an update in the source table:

CREATE OR REPLACE TRIGGER emp_source_update
AFTER UPDATE ON emp_source
FOR EACH ROW
BEGIN
    UPDATE emp_des
    SET name = :NEW.name,
        desig = :NEW.desig,
        meta_upd_dt = CURRENT_TIMESTAMP,
        meta_iud_flg = 'U'
    WHERE id = :OLD.id;
END;

and for a delete in the source table:

CREATE OR REPLACE TRIGGER emp_source_delete
AFTER DELETE ON emp_source
FOR EACH ROW
BEGIN
    UPDATE emp_des
    SET meta_upd_dt = CURRENT_TIMESTAMP,
        meta_iud_flg = 'D',
        current_flag = FALSE
    WHERE id = :OLD.id;
END;

NOTE These example are "rudimentary" and contain no error handling or signposting/notes. I am not proposing you use those triggers "as is" - a more complete trigger might look like this:

CREATE OR REPLACE TRIGGER emp_source_update
AFTER UPDATE ON emp_source
FOR EACH ROW
DECLARE
    -- Declare an exception for when no row is found in the emp_des table
    no_row_found EXCEPTION;
    PRAGMA EXCEPTION_INIT(no_row_found, -1403);
BEGIN
    UPDATE emp_des
    SET name = :NEW.name,
        desig = :NEW.desig,
        meta_upd_dt = CURRENT_TIMESTAMP,
        meta_iud_flg = 'U'
    WHERE id = :OLD.id;

    -- Check if a row was updated in the emp_des table
    IF SQL%ROWCOUNT = 0 THEN
        RAISE no_row_found;
    END IF;
EXCEPTION
    WHEN no_row_found THEN
        -- Handle the exception by inserting a new row into the emp_des table
        INSERT INTO emp_des (id, name, desig, META_CRT_DT, meta_upd_dt, meta_iud_flg, current_flag)
        VALUES (:NEW.id, :NEW.name, :NEW.desig, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, 'I', TRUE);
END;

but I'm not entirely sure what syntax is appropriate for you and there may be local standards to follow as well.

Thank you so much for the answer, I'll try to implement this. — TrenT, May 06 '23 at 07:07
Is there any way to do updates without triggers? Like update will take place only when i run the command? — TrenT, May 06 '23 at 13:27
Yes. But you won't capture the timestamps as accurately that way. You would create a "stored procedure" which holds the sql to be executed. This can then be run when you want it to, or perhaps on a regular cycle via a scheduler. But note that the logic to detect data change is much more complex without triggers. This is why I recommend them. — Paul Maxwell, May 06 '23 at 16:50
Can you point out any resource to how I can implement this with procedures? The end objective of this task given to me is to make me learn procedures, but I can't seem to find any resources with implementing this SCD type logic with the same. Thank you so much for your help so far. — TrenT, May 06 '23 at 18:45

score 1 · Accepted Answer · answered May 07 '23 at 05:35

You asked for a non-trigger approach (Is there any way to do updates without triggers? Like update will take place only when i run the command?). Whilst I strongly prefer a trigger based approach, here is a sample stored procedure:

Note that, unlike triggers, use of "current_timestamp" will not be when each individual event actually occurred, instead it will be when the stored procedure was executed, and these are not the same things at all.

CREATE OR REPLACE PROCEDURE update_emp_des()
AS
BEGIN
    -- Update existing records
    UPDATE emp_des
    SET desig = s.desig,
        meta_upd_dt = CURRENT_TIMESTAMP,
        meta_iud_flg = 'U',
        current_flag = TRUE
    FROM emp_source AS s
    WHERE emp_des.id = s.id AND emp_des.desig != s.desig;

    -- Insert new records
    INSERT INTO emp_des (id, name, desig, META_CRT_DT,
                         meta_upd_dt, meta_iud_flg, current_flag)
    SELECT id, name, desig,
           CURRENT_TIMESTAMP AS crt,
           CURRENT_TIMESTAMP AS upd,
           'I' AS iud, TRUE AS flag
    FROM emp_source
    WHERE NOT EXISTS (SELECT * FROM emp_des WHERE emp_source.id = emp_des.id);

    -- Delete records
    UPDATE emp_des
    SET meta_upd_dt = CURRENT_TIMESTAMP,
        meta_iud_flg = 'D',
        current_flag = FALSE
    WHERE NOT EXISTS (SELECT * FROM emp_source WHERE emp_source.id = emp_des.id);
END;

This stored procedure updates the desig, meta_upd_dt, and meta_iud_flg columns for existing records in the emp_des table where the desig column has changed in the emp_source table.

It also inserts new records from the emp_source table into the emp_des table and updates the meta_upd_dt, meta_iud_flg, and current_flag columns for records that have been deleted from the emp_source table.

You could break this stored proc into the 3 parts and rune them separately and this may be useful if volumes are large and you want to minimize any locking effects (another disadvantage of the stored proc approach is how to mange the locks).

Thank you so much, Paul. This is exacty what I wanted to implement. I really appreciate the time and effort you put into this. Thank you. — TrenT, May 07 '23 at 05:57

SQL INSERT, UPDATE and DELETE based on source table

2 Answers2