Friday, February 25, 2011

How to update date filed in Oracle like SQL



Update tableName set date =to_date(‘6/5/2011’,’mm/dd/yyyy’) where ---

A few more options for the update -


UPDATE employees SET
    job_id = 'SA_MAN', salary = salary + 1000, department_id = 120
    WHERE first_name||' '||last_name = 'Douglas Grant';


UPDATE employees a
    SET department_id =
        (SELECT department_id
            FROM departments
            WHERE location_id = '2100'),
        (salary, commission_pct) =
        (SELECT 1.1*AVG(salary), 1.5*AVG(commission_pct)
          FROM employees b
          WHERE a.department_id = b.department_id)
    WHERE department_id IN
        (SELECT department_id
          FROM departments
          WHERE location_id = 2900
              OR location_id = 2700);

UPDATE employees
   SET salary = salary * 1.1
   WHERE department_id = 100
   RETURNING SUM(salary) INTO :bnd1;


UPDATE employees
  SET job_id ='SA_MAN', salary = salary + 1000, department_id = 140
  WHERE last_name = 'Jones'
  RETURNING salary*0.25, last_name, department_id
    INTO :bnd1, :bnd2, :bnd3;

How to get top n Record in Oracle like Sql



Sample Query to get Top n Record from oracle

Select * from tableName where FiledName =”” and rownum<=5

The above statement will give u top 5 record
rownum<=5

A few more queries -

Not in

SELECT 'TRUE' 
    FROM emp 

    WHERE deptno NOT IN (5,15); 


LIKE Operator

SELECT sal 
    FROM emp 

    WHERE ename LIKE 'SM%';

Case Sensitivity and Pattern Matching

UPPER(ename) LIKE 'SM%'

Oracle UNION Example 

SELECT part, partnum, to_date(null) date_in
    FROM orders_list1
UNION
SELECT part, to_null(null), date_in
    FROM orders_list2;

Oracle UNION ALL Example 
Result - SELECT part 
    FROM orders_list1 
UNION ALL 
SELECT part 
    FROM orders_list2;

MINUS Example

SELECT part 
    FROM orders_list1 
MINUS 
SELECT part 
    FROM orders_list2;

INTERSECT  Example
SELECT part 
    FROM orders_list1 
INTERSECT 
SELECT part 

    FROM orders_list2;