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!
MaarvaadiAsked:
Who is Participating?
 
objectsCommented:
not that I am aware of unless there is an oracle specific command.

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.

0
 
MaarvaadiAuthor Commented:
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.
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
objectsCommented:
you should still be able to use a batch, the batch update returns an array of ints indicating the results of the statements

0
 
MaarvaadiAuthor Commented:
True, but then on failure you will have to backtrack and then start doing the inserts, but I get the idea. Thanks.
0
 
objectsCommented:
save you doing the select, unless oracle provides something then I don't know of a better way.

0
 
objectsCommented:
actually another way to look at it would be to try select for update, but you're going to  lose batching ability that way.

0
 
fsze88Commented:
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..
0
 
CEHJCommented:
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 (?,?,?,?,?,?)

Open in new window

0
 
MaarvaadiAuthor Commented:
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?
0
 
fsze88Commented:
something like code snippet
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;

Open in new window

0
 
MaarvaadiAuthor Commented:
fsze88: This has 2 tables. I have only one table which I am operating on.
0
 
fsze88Connect With a Mentor Commented:
Okey,
please have a look http://www.psoug.org/reference/merge.html
;)
0
 
fsze88Commented:
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>

Open in new window

0
 
objectsCommented:
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.

0
 
CEHJCommented:
:-)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.