how to insert and update with single statement...? [message #546123] |
Mon, 05 March 2012 07:04 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/038f5b05235020d428a43420fd099c51?s=64&d=mm&r=g) |
kartheek
Messages: 17 Registered: February 2012 Location: chennai
|
Junior Member |
|
|
hi all ,
I need a sql statemnet . for the below scenario...
I would like to insert a value if that value is not existing in the table (example for a column which contains date only new dates should be inserted and if the date already exists in the column then it needs to get updated ) can any one help me out in this please..?
thanks in advance..
example of scenario...
date s1 s2 s3
in the above if the date is new..it should get inserted with the appropriate slot no.(s1,s2,s3)
if the date already exists it needs to update the no.in slot no.
|
|
|
|
|
|
|
Re: how to insert and update with single statement...? [message #546238 is a reply to message #546123] |
Mon, 05 March 2012 21:55 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
mvmkandan
Messages: 68 Registered: May 2010 Location: Trivendrum
|
Member |
|
|
MERGE STATEMENT is used for Update of the condition is matched or insert.
MERGE <hint> INTO <table_name>
USING <table_view_or_query>
ON (<condition>)
WHEN MATCHED THEN <update_clause>
DELETE <where_clause>
WHEN NOT MATCHED THEN <insert_clause>
[LOG ERRORS <log_errors_clause> <reject limit <integer | unlimited>];
CREATE TABLE employee (
employee_id NUMBER(5),
first_name VARCHAR2(20),
last_name VARCHAR2(20),
dept_no NUMBER(2),
salary NUMBER(10));
INSERT INTO employee VALUES (1, 'Dan', 'Morgan', 10, 100000);
INSERT INTO employee VALUES (2, 'Helen', 'Lofstrom', 20, 100000);
INSERT INTO employee VALUES (3, 'Akiko', 'Toyota', 20, 50000);
INSERT INTO employee VALUES (4, 'Jackie', 'Stough', 20, 40000);
INSERT INTO employee VALUES (5, 'Richard', 'Foote', 20, 70000);
INSERT INTO employee VALUES (6, 'Joe', 'Johnson', 20, 30000);
INSERT INTO employee VALUES (7, 'Clark', 'Urling', 20, 90000);
CREATE TABLE bonuses (
employee_id NUMBER, bonus NUMBER DEFAULT 100);
INSERT INTO bonuses (employee_id) VALUES (1);
INSERT INTO bonuses (employee_id) VALUES (2);
INSERT INTO bonuses (employee_id) VALUES (4);
INSERT INTO bonuses (employee_id) VALUES (6);
INSERT INTO bonuses (employee_id) VALUES (7);
COMMIT;
SELECT * FROM employee;
SELECT * FROM bonuses;
MERGE INTO bonuses b
USING (
SELECT employee_id, salary, dept_no
FROM employee
WHERE dept_no =20) e
ON (b.employee_id = e.employee_id)
WHEN MATCHED THEN
UPDATE SET b.bonus = e.salary * 0.1
DELETE WHERE (e.salary < 40000)
WHEN NOT MATCHED THEN
INSERT (b.employee_id, b.bonus)
VALUES (e.employee_id, e.salary * 0.05)
WHERE (e.salary > 40000);
SELECT * FROM bonuses;
Veera
[Updated on: Mon, 05 March 2012 21:56] Report message to a moderator
|
|
|
Re: how to insert and update with single statement...? [message #546240 is a reply to message #546238] |
Mon, 05 March 2012 22:17 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
sandeepgujje
Messages: 28 Registered: January 2012 Location: India
|
Junior Member |
|
|
DECLARE
V_Count NUMBER(2);
BEGIN
SELECT COUNT(*)
INTO V_COUNT
FROM TABLE_NAME
WHERE TO_CHAR(SDATE, 'DD-MON-YYYY') = TO_CHAR(:BLOCK_NAME.DATE, 'DD-MON-YYYY);
IF SQL%NOTFOUND THEN
-- WRITE AN INSERT --
ELSE
-- WRITE AN UPDATE --
END IF;
END;
Hope this one will give you an idea..
|
|
|
|
Re: how to insert and update with single statement...? [message #546287 is a reply to message #546247] |
Tue, 06 March 2012 01:57 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/72104.gif) |
Littlefoot
Messages: 21818 Registered: June 2005 Location: Croatia, Europe
|
Senior Member Account Moderator |
|
|
Generally speaking: if something can be done in SQL, you should do it - rather than using PL/SQL. Generally speaking, SQL will be faster than PL/SQL. Generally speaking, it will take more time to write the same (complex) logic in SQL than in PL/SQL, but - you'll benefit from it in a long term.
|
|
|