Re: How can we delete the record in table with the help of update command( Without using delete command)???

[ddf]

On Jul 4, 11:41 am, nitesh dimri <niteshdi...@gmail.com> wrote:
> How can we delete the record in table with the help of  update command(
> Without using delete command)???

What, EXACTLY, do you mean by such a question? Updating a record
won't delete it as it will still be present in the table. Do you need
to change the key for that record? That you can do with update, but
if it is a primary key update Oracle will delete the old record and
insert a new record with the proper key.

Please explain PRECISELY what you want/need to do as your question
makes absolutely no sense as it is.


David Fitzjarrell

[nitesh dimri]

David Fitzjarrell,

I am doing M.C.A and this question was asked by the interviewer during
placement. So, I asked this question to the group. As it seems to be a
wrong question. I myself try to solve this question but fails. Sir can
you please tell which certification has better scope in future Oracle
Database 11g: Advanced PL/SQL or Oracle Forms Developer Certified
Professional?

Thanks for the reply

Nitesh Dimri

> --
> You received this message because you are subscribed to the Google Groups
> "ORACLE_DBA_EXPERTS" group.
> To post to this group, send email to oracle_db...@googlegroups.com.
> To unsubscribe from this group, send email to
> oracle_dba_expe...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/oracle_dba_experts?hl=en.
>
>

[jgar the jorrible]

On Jul 5, 12:18 pm, nitesh dimri <niteshdi...@gmail.com> wrote:
> David Fitzjarrell,
>
> I am doing M.C.A and this question was asked by the interviewer during
> placement. So, I asked this question to the group. As it seems to be a
> wrong question. I myself try to solve this question but fails. Sir can
> you please tell which certification has better scope in future Oracle
> Database 11g: Advanced PL/SQL or Oracle Forms Developer Certified
> Professional?
>
> Thanks for the reply
>
> Nitesh Dimri

I think David answered the technical question, though there might be
some specific thing in the way you were asked that made it a better or
worse question.

As far as Forms v. PL/SQL, Forms is old technology, which may be
useful for getting your foot in the door in an existing shop,
especially if you have a desire for functional analysis rather than
deep programming. If you have to choose between those two certs, PL
would likely be more useful overall. See
http://www.oracle.com/technetwork/issue-archive/2010/toolssod-3-129969.pdf
(found by googling: oracle statement of direction forms)

jg
--
@home.com is bogus.
Larry would only be the second most annoying cow-orker:
http://www.utsandiego.com/news/2012/jul/04/which-comic-hero-would-make-most-annoying-coworker/

[nitesh dimri]

Thank you sir,

Nitesh Dimri

[ddf]

On Jul 5, 5:39 pm, nitesh dimri <niteshdi...@gmail.com> wrote:
> Thank you sir,
>
> Nitesh Dimri

I think I finally understand the question and yes, it's possible to
'delete' a record using an update statement:

EMPNO ENAME JOB MGR HIREDATE SAL
COMM DEPTNO
---------- ---------- --------- ---------- --------- ----------
---------- ----------
7369 SMITH CLERK 7902 17-DEC-80
800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600
300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250
500 30
7566 JONES MANAGER 7839 02-APR-81
2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250
1400 30
7698 BLAKE MANAGER 7839 01-MAY-81
2850 30
7782 CLARK MANAGER 7839 09-JUN-81
2450 10
7788 SCOTT ANALYST 7566 09-DEC-82
3000 20
7839 KING PRESIDENT 17-NOV-81
5000 10
7844 TURNER SALESMAN 7698 08-SEP-81
1500 0 30
7876 ADAMS CLERK 7788 12-JAN-83
1100 20

EMPNO ENAME JOB MGR HIREDATE SAL
COMM DEPTNO
---------- ---------- --------- ---------- --------- ----------
---------- ----------
7900 JAMES CLERK 7698 03-DEC-81
950 30
7902 FORD ANALYST 7566 03-DEC-81
3000 20
7934 MILLER CLERK 7782 23-JAN-82
1300 10

14 rows selected.

SQL>
SQL> update emp
2 set empno = 7999, ename='SPLODGET', hiredate=add_months(sysdate,
-2), comm=350
3 where empno = 7654;

1 row updated.

SQL>
SQL> select * from emp;

EMPNO ENAME JOB MGR HIREDATE SAL
COMM DEPTNO
---------- ---------- --------- ---------- --------- ----------
---------- ----------
7369 SMITH CLERK 7902 17-DEC-80
800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600
300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250
500 30
7566 JONES MANAGER 7839 02-APR-81
2975 20
7999 SPLODGET SALESMAN 7698 06-MAY-12 1250
350 30
7698 BLAKE MANAGER 7839 01-MAY-81
2850 30
7782 CLARK MANAGER 7839 09-JUN-81
2450 10
7788 SCOTT ANALYST 7566 09-DEC-82
3000 20
7839 KING PRESIDENT 17-NOV-81
5000 10
7844 TURNER SALESMAN 7698 08-SEP-81
1500 0 30
7876 ADAMS CLERK 7788 12-JAN-83
1100 20

EMPNO ENAME JOB MGR HIREDATE SAL
COMM DEPTNO
---------- ---------- --------- ---------- --------- ----------
---------- ----------
7900 JAMES CLERK 7698 03-DEC-81
950 30
7902 FORD ANALYST 7566 03-DEC-81
3000 20
7934 MILLER CLERK 7782 23-JAN-82
1300 10

14 rows selected.

SQL>

Notice that employee 7654, named MARTIN, no longer exists in the
table, replaced by employee number 7699 named SPLODGET, with a new
hire date and commission (she works for the same manager in the same
department that MARTIN did). No delete was executed but the record
for that former employee is 'gone'.

Of course this is NOT the way to manage an HR table of employees --
one would want to use the delete command to remove an employee record
provided a transactional history is maintained of all inserts, updates
and deletes so an audit trail can be established. This is simply an
example to show that it can be done.


David Fitzjarrell

[ddf]

Typo in my response -- should have read 'replaced by employee number
7999 ...'.


David Fitzjarrell

[nitesh dimri]

Hello sir,
Can you please tell me books for advance plsql. I have completed the
topics of plsql. Can you please suggest me some books for advance
plsql.

Nitesh Dimri