Oracle - Update records of a table using where Exists
clause - April 30, 2009 at 20:00 PM by Amit Satpute
Explain how to update records of a table in oracle using where Exists clause.
Consider two Tables A and B as follows:
Tables A
| ID |
Name |
| 1 |
ABC |
| 2 |
EFG |
| 3 |
MNO |
| 4 |
XYZ |
Table B
| ID |
Name |
| 1 |
DEF |
| 2 |
KLM |
| 3 |
PQR |
| 5 |
UVW |
Query without ‘exists’:
update B b set b.name = (select name from A a where a.id = b.id);
Result
| ID |
Name |
| 1 |
ABC |
| 2 |
EFG |
| 3 |
MNO |
| 5 |
UVW |
Query with ‘exists’:
Update B b set b.name = (select name from A a where a.id = b.id) Where exists
(select l from A a where a.id = b.id);
Result
| ID |
Name |
| 1 |
ABC |
| 2 |
EFG |
| 3 |
MNO |
| 5 |
UVW |
Also read
What a SELECT FOR UPDATE cursor represent?, What WHERE CURRENT OF clause does in
a cursor?, Can you pass a parameter to a cursor?, Explain the functioning of
CURSOR FOR LOOP with example., Define Simple/Explicit , Parametric and
Internal/Implicit cursor.............
Latches are used to protect Oracle data structures to be modified or run by more
than one process. They are more restrictive than locks. Locking has a similar
concept...............
User process – User process is used in invocation of application software, Data
writing process - A database writer process is used to write buffer content
into a datafile. They are specifically used to write “dirty block” to data
files from the buffer...............
Table references can be complicated. So oracle allows you create synonym for a
complicated reference. It renames a table reference..........
Explain drop and truncate table command, Write the command to view the structure
of the table, What are the limitation of alter command?, Explain Alter Table
Command. What are the limitations of Alter Table command?............
|
