1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

how to delete record

Discussion in 'SQL PL/SQL' started by laxman, Nov 9, 2009.

  1. laxman

    laxman Forum Expert

    Messages:
    144
    Likes Received:
    0
    Trophy Points:
    230
    Location:
    MUMBAI
    Dear sir,
    i have some few doubts mentioned below,

    1) how to delete last n records
    2) how to insert records in multiple table at one sorts.
    3) how to delete records in multiple table at one sorts.
     
  2. Sadik

    Sadik Community Moderator Forum Guru

    Messages:
    1,906
    Likes Received:
    252
    Trophy Points:
    1,455
    You should state your doubts with examples. What you have asked can have multiple solutions depending on the tables, data etc.
     
  3. laxman

    laxman Forum Expert

    Messages:
    144
    Likes Received:
    0
    Trophy Points:
    230
    Location:
    MUMBAI
    Dear sir,
    i have some few doubts mentioned below,

    1) how to delete last n records
    example:let say a table contains 100 records with empid and empname then i wanted to delete last 10 records
    2) how to insert records in multiple table at one sorts.
    example:suppose i have two table with parent-child relationship on that condition i want to insert records
    3) how to delete records in multiple table at one sorts.
    example:suppose i have two table with parent-child relationship on that condition i want to delete records.
    kindly help me.

    Regards
    Laxman
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    'last 10 records' with respect to what? EMPID? EMPNAME? You need to be more specific.

    INSERT ALL works nicely for multi-table inserts:

    insert all
    into tab1 values ...
    into tab2 values ...
    ...
    select * from dual;

    I think for the third you'll need to delete the child records then the parant records, unless you set up the foreign key with ON DELETE CASCADE which will delete child and parent records.
     
  5. laxman

    laxman Forum Expert

    Messages:
    144
    Likes Received:
    0
    Trophy Points:
    230
    Location:
    MUMBAI
    Dear sir,
    for both condition i want to get clarify, i.e
    1> with respect to empid
    2> with respect to ename.
    kindly do the needful

    Thanks and regards
    Laxman
     
  6. debasisdas

    debasisdas Active Member

    Messages:
    46
    Likes Received:
    3
    Trophy Points:
    90
    Location:
    Bangalore, India
    for your question 2 & 3 it will be better to use procedure.
    while inserting 1st insert into parent table and then into child table
    while deleting 1st delete from child and then delete from parent table.
     
  7. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Why use a procedure for the inserts? INSERT ALL works quite nicely:

    CREATE TABLE DEPT
    (DEPTNO NUMBER(2),
    DNAME VARCHAR2(14),
    LOC VARCHAR2(13),
    CONSTRAINT DEPT_PK PRIMARY KEY(DEPTNO));

    CREATE TABLE EMP
    (EMPNO NUMBER(4) NOT NULL,
    ENAME VARCHAR2(10),
    JOB VARCHAR2(9),
    MGR NUMBER(4),
    HIREDATE DATE,
    SAL NUMBER(7, 2),
    COMM NUMBER(7, 2),
    DEPTNO NUMBER(2),
    CONSTRAINT EMP_PK PRIMARY KEY(EMPNO),
    CONSTRAINT EMP_DEPT_FK FOREIGN KEY(DEPTNO)
    REFERENCES DEPT);

    INSERT ALL
    INTO DEPT VALUES (10, 'ACCOUNTING', 'NEW YORK')
    INTO DEPT VALUES (20, 'RESEARCH', 'DALLAS')
    INTO DEPT VALUES (30, 'SALES', 'CHICAGO')
    INTO DEPT VALUES (40, 'OPERATIONS', 'BOSTON')
    INTO EMP VALUES
    (7369, 'SMITH', 'CLERK', 7902,
    TO_DATE('17-DEC-1980', 'DD-MON-YYYY'), 800, NULL, 20)
    INTO EMP VALUES
    (7499, 'ALLEN', 'SALESMAN', 7698,
    TO_DATE('20-FEB-1981', 'DD-MON-YYYY'), 1600, 300, 30)
    INTO EMP VALUES
    (7521, 'WARD', 'SALESMAN', 7698,
    TO_DATE('22-FEB-1981', 'DD-MON-YYYY'), 1250, 500, 30)
    INTO EMP VALUES
    (7566, 'JONES', 'MANAGER', 7839,
    TO_DATE('2-APR-1981', 'DD-MON-YYYY'), 2975, NULL, 20)
    INTO EMP VALUES
    (7654, 'MARTIN', 'SALESMAN', 7698,
    TO_DATE('28-SEP-1981', 'DD-MON-YYYY'), 1250, 1400, 30)
    INTO EMP VALUES
    (7698, 'BLAKE', 'MANAGER', 7839,
    TO_DATE('1-MAY-1981', 'DD-MON-YYYY'), 2850, NULL, 30)
    INTO EMP VALUES
    (7782, 'CLARK', 'MANAGER', 7839,
    TO_DATE('9-JUN-1981', 'DD-MON-YYYY'), 2450, NULL, 10)
    INTO EMP VALUES
    (7788, 'SCOTT', 'ANALYST', 7566,
    TO_DATE('09-DEC-1982', 'DD-MON-YYYY'), 3000, NULL, 20)
    INTO EMP VALUES
    (7839, 'KING', 'PRESIDENT', NULL,
    TO_DATE('17-NOV-1981', 'DD-MON-YYYY'), 5000, NULL, 10)
    INTO EMP VALUES
    (7844, 'TURNER', 'SALESMAN', 7698,
    TO_DATE('8-SEP-1981', 'DD-MON-YYYY'), 1500, 0, 30)
    INTO EMP VALUES
    (7876, 'ADAMS', 'CLERK', 7788,
    TO_DATE('12-JAN-1983', 'DD-MON-YYYY'), 1100, NULL, 20)
    INTO EMP VALUES
    (7900, 'JAMES', 'CLERK', 7698,
    TO_DATE('3-DEC-1981', 'DD-MON-YYYY'), 950, NULL, 30)
    INTO EMP VALUES
    (7902, 'FORD', 'ANALYST', 7566,
    TO_DATE('3-DEC-1981', 'DD-MON-YYYY'), 3000, NULL, 20)
    INTO EMP VALUES
    (7934, 'MILLER', 'CLERK', 7782,
    TO_DATE('23-JAN-1982', 'DD-MON-YYYY'), 1300, NULL, 10)
    SELECT * FROM DUAL;

    No need for a procedure to perform a simple insert into multiple tables.
     
  8. laxman

    laxman Forum Expert

    Messages:
    144
    Likes Received:
    0
    Trophy Points:
    230
    Location:
    MUMBAI
    dear sir,
    Thanks for your valuable information.only one doubt i have left with i.e how to delete last n records from a table or how to select last n records,kindly help me in this issue.

    Regards
    Laxman
     
  9. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Rows are not ordered in a heap table so there is no 'last row' in such an object. Order is up to you as to how you want the rows to be returned. I suggest you read here:

    http://oratips-ddf.blogspot.com/2008/06/row-row-row.html