• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 797
  • Last Modified:

HOW TO WRITE sql script to delete records ?

I have 3 employee tables with employee_name as Primary Key.
        The table structures are as follows.
      1) desc employee_info
            Emp_name      Varchar2(150),
            DOB            Date,            sex            Varchar2(10),
            date_joined      Date,
            date_resigned      Date
      2) desc employee_pay
            Emp_name      Varchar2(150),
            salary            Number,
            allowance      Number,
                Temp_or_perm      Varchar2(1)
      3) desc employee_location
            Emp_name      Varchar2(150),
            region            Varchar2(100),
            city            Varchar2(100),
            country            Varchar2(100),
            zip            Number
      I need a pl\sql scripts for
      a) to delete the employee records from the above 3 tables
         who resigned on or earlier to Feb 1st (<= 02/01/2012)

      b) script for the above requirement with  deleted records
           storing in temprary tables.
0
Akai123
Asked:
Akai123
  • 5
  • 2
  • 2
1 Solution
 
sdstuberCommented:
If you want to keep the data, you'll have to create the tables first..
So you'll need to run something like this....


CREATE TABLE employee_info_backup
AS
    SELECT *
      FROM employee_info
     WHERE 1 = 0;

CREATE TABLE employee_pay_backup
AS
    SELECT *
      FROM employee_pay
     WHERE 1 = 0;

CREATE TABLE employee_location_backup
AS
    SELECT *
      FROM employee_location
     WHERE 1 = 0;

Open in new window



You can use this script to solve both a) and b),  
for  b), simply set v_backup to TRUE instead of FALSE


DECLARE
    v_threshold_date DATE := TO_DATE('2012-02-01 23:59:59', 'yyyy-mm-dd hh24:mi:ss') + 1;
    v_backup         BOOLEAN := FALSE;
BEGIN
    IF v_backup
    THEN
        INSERT INTO employee_info_backup
            SELECT *
              FROM employee_info
             WHERE date_resigned <= v_threshold_date;

        INSERT INTO employee_pay_backup
            SELECT *
              FROM employee_pay
             WHERE emp_name IN (SELECT emp_name
                                  FROM employee_info
                                 WHERE date_resigned <= v_threshold_date);

        INSERT INTO employee_location_backup
            SELECT *
              FROM employee_location
             WHERE emp_name IN (SELECT emp_name
                                  FROM employee_info
                                 WHERE date_resigned <= v_threshold_date);
    END IF;

    DELETE employee_pay
     WHERE emp_name IN (SELECT emp_name
                          FROM employee_info
                         WHERE date_resigned <= v_threshold_date);

    DELETE employee_location
     WHERE emp_name IN (SELECT emp_name
                          FROM employee_info
                         WHERE date_resigned <= v_threshold_date);

    DELETE employee_info
     WHERE date_resigned <= v_threshold_date;

    COMMIT;
END;

Open in new window

0
 
Andrei FomitchevCommented:
The request was about ORACLE and PL/SQL - not MS SQL and T-SQL.
0
 
sdstuberCommented:
the code above is Oracle PL/SQL

why would you think otherwise?
0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
Akai123Author Commented:
can we make the above 2 scripts as one like --- create statements for backup tables
on top of the pl/sql anonymous block,
0
 
Andrei FomitchevCommented:
DELETE FROM employee_info goes last in case you have foreign keys to it from pay and/or location.

I haven't seen previous comments while I was writing mine.
My solution has less "date conditions" - it will work faster.
employee_info_deleted provides deleted Emp_name(s).

CREATE TABLE employee_info_deleted AS 
SELECT * FROM employee_info 
WHERE  date_resigned <= TO_DATE('2012-02-01', 'yyyy-mm-dd')

CREATE TABLE employee_pay_deleted AS 
SELECT * FROM employee_pay 
WHERE Emp_name IN 
(SELECT Emp_name FROM employee_info_deleted)

CREATE TABLE employee_location_deleted AS 
SELECT * FROM employee_location 
WHERE Emp_name IN 
(SELECT Emp_name FROM employee_info_deleted)

DELETE FROM employee_pay
WHERE Emp_name IN 
(SELECT Emp_name FROM employee_info_deleted)

DELETE FROM employee_location
WHERE Emp_name IN 
(SELECT Emp_name FROM employee_info_deleted)

DELETE FROM employee_info
WHERE Emp_name IN 
(SELECT Emp_name FROM employee_info_deleted)

Open in new window


About ORACLE vs MS SQL - it didn't say that someone wrote MS SQL. If it abused someone - sorry.
0
 
sdstuberCommented:
you can put the create table statements inside an execute immediate


for example...

execute immediate 'CREATE TABLE employee_pay_backup AS SELECT * FROM employee_pay WHERE 1 = 0';

note, the semicolon does not go inside the quotes
0
 
Akai123Author Commented:
Hi sdstuber,
I like your script and  I want to know how to incorporate rollback in case if the script is terminated by some unknown reason without performing  completely. Any  exceptions
are required to add to the script.
Thanks sdstuber,
0
 
sdstuberCommented:
Change the end of the script to include whatever exception handlers you want

    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
           ROLLBACK;
           dbms_output.put_line(SQLERRM);  -- change and add other functionality as needed
END;

Open in new window

0
 
sdstuberCommented:
why the B?  

a penalty grade is not appropriate
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 5
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now