Oraclesteps

How to update a rows with opposite gender in Sql?

2014-09-13T04:34:00.004-07:00

SQL :> Select Decode (Gender,Male,Female,Female,Male) From Table_Name;

what is external tables and how and inwhich case we should use this?

2012-08-28T05:27:00.004-07:00

external tables are mainly used when we have the data in flat files and we need to import that data into database then we go for external tables.

eg xml files, csv files, etc.

Using external tables as server face you can define table using position and access data same as structured table, using dml. Perhaps good use is to load external data without use loader with temporal table in order to make validations. You can access records validate it and load directly.

what is diff bet sub-query and corrrelated sub-query?

2012-08-27T06:15:00.002-07:00

Sub-query - The inner query execute first, the outer query deprnds on inner query output.
Correlated sub-query - The outer query execute first, the inner query deprnds on outer query result.

A correlated sub-query is where the sub-query part makes reference to values from the outer query.

------------------------------------------------------------------------------------------------------------------------------

SELECT emp1.emp_id, emp1.last_name, emp1.job_id, emp1.department_id from employee emp1

WHERE EXISTS (SELECT ‘X’ FROM employee emp2 WHERE emp2.manager_id = emp1.emp_ID);

Is a correlated sub-query because of the reference to emp1.emp_ID in the sub- query. The sub-query is evaluated once for each row processed by the outer query.

------------------------------------------------------------------------------------------------------------------------------

SELECT e1.employee_number, e1.name FROM employee e1

WHERE e1.salary > (SELECT avg(e2.salary) FROM e2.employee);

Is not a correlated sub-query because the sub-query makes no reference to the values of the outer part of the query.

Removing Duplicate Rows from a Table.

2012-08-27T05:56:00.003-07:00

Use rowid pseudo column. All you have to do is to keep the latest data (i.e. highest ROWID) and remove other duplicated rows.
SELECT * FROM table1 a
WHERE rowid < (SELECT max(rowid) FROM table1 b
WHERE a.column1 = b.column1 AND etc...);

OR

create table testtt (num number);

insert into testtt values(111);
insert into testtt values(111);
insert into testtt values(111);
insert into testtt values(111);
insert into testtt values(222);
insert into testtt values(222);
insert into testtt values(333);
insert into testtt values(333);
insert into testtt values(333);

select * from testtt;

delete from testtt
where (rowid, num) not in (select max_rid, num
from (select num,
count(num) over (partition by num) cnt,
max(rowid) over (partition by num) max_rid
from testtt)
where cnt > 1);

select * from testtt;

OR

While I doubt this method has any advantages over another, it's an example:
DELETE FROM table_a
WHERE rowid IN
( SELECT rowid FROM table_a
MINUS
SELECT MAX( rowid ) FROM table_a
GROUP BY column_list )

OR

delete from table_name where rowid not in (select max(rowid) from table group byduplicate_values_field_name);

OR

highest rowid does not necessarily mean latest data... since space freed from deleting rows might be reused.

SQL> CREATE TABLE t AS SELECT level l FROM DUAL CONNECT BY LEVEL <= 5000;

SQL> DELETE FROM t WHERE l < 5000;

SQL> COMMIT;

SQL> INSERT INTO t VALUES (5001);

SQL> COMMIT;

SQL> SELECT max(l) KEEP(DENSE_RANK LAST ORDER BY rowid) as maxrid, max(l) KEEP(DENSE_RANK FIRST ORDER BY rowid) minrid FROM t;

MAXRID MINRID
---------- ----------
5000 5001

Updating Multiple columns using values from another column.

2012-08-27T05:53:00.002-07:00

update table_name
set (col1,col2,col3)=(select t.col4,t.col5,t.col6 from table_name2 t where something)

OR

create table tgt ( id number /*primary key*/, x number, y number )
/
insert into tgt ( id, x, y )
select level, level, level from dual connect by level < 4
/
create table src ( id number /*primary key*/, x number, y number )
/
insert into src ( id, x, y )
select level+1, level*5, level*5 from dual connect by level < 3
/
commit
/
select * from src;

ID X Y
---------- ---------- ----------
2 5 5
3 10 10

select * from tgt;

ID X Y
---------- ---------- ----------
1 1 1
2 2 2
3 3 3
UPDATE tgt
SET ( tgt.x, tgt.y ) =
( SELECT src.x, src.y
FROM src
WHERE src.id = tgt.id
)
WHERE EXISTS
( SELECT src.x, src.y
FROM src
WHERE src.id = tgt.id
)
/

2 rows updated.

select * from tgt;

ID X Y
---------- ---------- ----------
1 1 1
2 5 5
3 10 10

OR

Provided that you have a unique constraint on join columns, you can directly update the view.
Adding PK to Guru's example :
ALTER TABLE src ADD CONSTRAINT pksrc PRIMARY KEY (id);

Update :
UPDATE
(SELECT tgt.id, tgt.x, tgt.y, src.x as srcx, src.y as srcy
FROM tgt
JOIN src ON tgt.id = src.id)
SET x = srcx, y = srcy;

SQL> select * from tgt;

ID X Y
---------- ---------- ----------
1 1 1
2 5 5
3 10 10

(But if you do not have the constraint, you get "not key preserved table" error)

How to track last update date on a particular record without DBA help.

2012-08-27T05:48:00.003-07:00

we can get the last updated date based on the following inbuild function:

scn_to_timestamp(max(ora_rowscn))

Example:

create table Test_last_upd
(a number,
b varchar2(3)
);

begin
insert into Test_last_upd values (1,'AAA');
insert into Test_last_upd values (2,'BBB');
insert into Test_last_upd values (3,'CCC');
end;

update Test_last_upd
set b = 'ABC'
where a =3;

select TLU.*, scn_to_timestamp(max(ora_rowscn))
from Test_last_upd TLU
group by a,b;