Maarvaadi
asked on
Oracle Insert/update in Java
I have a USER table in Oracle with the following columns
FIRST_NAME char
LAST_NAME char
ID number
DEPT number
DATE_CREATED date
DATE_MODIFIED date
The database can have these records and I am given a set of IDS and names to update the DEPT field to a new number. Is there a single command I can user to insert/or update the record given an ID? In other words:
if the record exist,
then update DEPT and set DATE_MODIFIED
else
INSERT with all values.
Is there a simple way to do it other than doing a select for it and then deciding to insert or not?
Thanks!
FIRST_NAME char
LAST_NAME char
ID number
DEPT number
DATE_CREATED date
DATE_MODIFIED date
The database can have these records and I am given a set of IDS and names to update the DEPT field to a new number. Is there a single command I can user to insert/or update the record given an ID? In other words:
if the record exist,
then update DEPT and set DATE_MODIFIED
else
INSERT with all values.
Is there a simple way to do it other than doing a select for it and then deciding to insert or not?
Thanks!
ASKER
The problem with that is then I cant batch them. I will have to update them one by one. Otherwise I can batch a 1000 updates and or inserts and then execute them at one shot increasing performance.
you should still be able to use a batch, the batch update returns an array of ints indicating the results of the statements
ASKER
True, but then on failure you will have to backtrack and then start doing the inserts, but I get the idea. Thanks.
save you doing the select, unless oracle provides something then I don't know of a better way.
actually another way to look at it would be to try select for update, but you're going to lose batching ability that way.
actually, this question is related to oracle more than java
create a procedure/function from oracle and run it by java command
procedure/function do the jobs what you needs (select/update/insert)
It could saving cpu usage on both of java client application and oracle database server..
create a procedure/function from oracle and run it by java command
procedure/function do the jobs what you needs (select/update/insert)
It could saving cpu usage on both of java client application and oracle database server..
Use a PreparedStatement, the sql of which should be something like the below:
MERGE INTO user
USING (
SELECT id, dept, date_modified
FROM user
WHERE id = ? )
WHEN MATCHED THEN
UPDATE SET dept = ?, date_modified = ?
WHEN NOT MATCHED THEN
INSERT (first_name, last_name, id, dept, date_created, date_modified) values (?,?,?,?,?,?)ASKER
CEHJ: Thanks for that pointer, but apparantly that does not work. I did a search for the syntax and it is like this:
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);
Which means that there is 2 tables involved?
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);
Which means that there is 2 tables involved?
something like code snippet
reference http://searchoracle.techtarget.com/expert/KnowledgebaseAnswer/0,289625,sid41_gci1225807,00.html
reference http://searchoracle.techtarget.com/expert/KnowledgebaseAnswer/0,289625,sid41_gci1225807,00.html
Merge into destination_table d
using source_table s
on (d.unique_id = s.unique_id)
when matched then
Update
set d.full_name = s.full_name,
d.phone = s.phone,
d.email = s. email
when not matched then
insert (d.contact_id,
d.creation_date,
d.full_name,
d.phone,
d.email,
d.unique_id)
values (v_next_val,
s.creation_date,
s.full_name,
s.phone,
s.email,
s.unique_id);
commit;ASKER
fsze88: This has 2 tables. I have only one table which I am operating on.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
it works fine on my oracle 10g XE
SQL> CREATE TABLE employee (
2 employee_id NUMBER(5),
3 first_name VARCHAR2(20),
4 last_name VARCHAR2(20),
5 dept_no NUMBER(2),
6 salary NUMBER(10));
òúËh<.
SQL> INSERT INTO employee VALUES (1, 'Dan', 'Morgan', 10, 100000);
òúË 1 Ç™.
SQL> INSERT INTO employee VALUES (2, 'Jack', 'Cline', 20, 100000);
òúË 1 Ç™.
SQL> INSERT INTO employee VALUES (3, 'Helen', 'Lofstrom', 20, 50000);
òúË 1 Ç™.
SQL> INSERT INTO employee VALUES (4, 'Jackie', 'Stough', 20, 40000);
òúË 1 Ç™.
SQL> INSERT INTO employee VALUES (5, 'Richard', 'Foote', 20, 70000);
òúË 1 Ç™.
SQL> INSERT INTO employee VALUES (6, 'Joe', 'Johnson', 20, 30000);
òúË 1 Ç™.
SQL> INSERT INTO employee VALUES (7, 'Clark', 'Urling', 20, 90000);
òúË 1 Ç™.
SQL>
SQL> CREATE TABLE bonuses (
2 employee_id NUMBER, bonus NUMBER DEFAULT 100);
òúËh<.
SQL> INSERT INTO bonuses (employee_id) VALUES (1);
òúË 1 Ç™.
SQL> INSERT INTO bonuses (employee_id) VALUES (2);
òúË 1 Ç™.
SQL> INSERT INTO bonuses (employee_id) VALUES (4);
òúË 1 Ç™.
SQL> INSERT INTO bonuses (employee_id) VALUES (6);
òúË 1 Ç™.
SQL> INSERT INTO bonuses (employee_id) VALUES (7);
òúË 1 Ç™.
SQL> COMMIT;
ºŒ.
SQL> MERGE INTO bonuses b
2 USING (
3 SELECT employee_id, salary, dept_no
4 FROM employee
5 WHERE dept_no =20) e
6 ON (b.employee_id = e.employee_id)
7 WHEN MATCHED THEN
8 UPDATE SET b.bonus = e.salary * 0.1
9 DELETE WHERE (e.salary < 40000)
10 WHEN NOT MATCHED THEN
11 INSERT (b.employee_id, b.bonus)
12 VALUES (e.employee_id, e.salary * 0.05)
13 WHERE (e.salary > 40000);
u 6 Ç™.
SQL> SELECT * FROM bonuses;
EMPLOYEE_ID BONUS
----------- ----------
1 100
2 10000
4 4000
7 9000
5 3500
3 2500
òxÖ 6 Ç™.
SQL> MERGE INTO bonuses b
2 USING (
3 SELECT employee_id, salary, dept_no
4 FROM employee
5 WHERE dept_no =20) e
6 ON (b.employee_id = e.employee_id)
7 WHEN MATCHED THEN
8 UPDATE SET b.bonus = e.salary * 0.1
9 DELETE WHERE (e.salary < 40000)
10 WHEN NOT MATCHED THEN
11 INSERT (b.employee_id, b.bonus)
12 VALUES (e.employee_id, e.salary * 0.05)
13 WHERE (e.salary > 40000);
u 5 Ç™.
SQL> SELECT * FROM bonuses;
EMPLOYEE_ID BONUS
----------- ----------
1 100
2 10000
4 4000
7 9000
5 7000
3 5000
òxÖ 6 Ç™.
SQL>
told you, you would need an oracle specific solution :)
good to know it can be done like that, we'll update our knowledge base accordingly.
good to know it can be done like that, we'll update our knowledge base accordingly.
:-)
instead of doing a select you could attempt the update and check if the record was updated (executeUpdate() returns the number od rows updated). If not updated then its safe to assume it needs to be inserted.