Insert or update command a row

I am looking for a statement which would update a field in a table if the row is present or insert if it is not there. I looked at this command :

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);


But this seems to be merging netween 2 tables, I want something like above for one table. Is there such a beast?
MaarvaadiAsked:
Who is Participating?
 
flow01Commented:
Your java data are probably the second table : I don't  know of java-syntax but can you construct something like
 
MERGE INTO bonuses b
USING (
  SELECT :java.employee_id, :java.salary, :java.dept_no
  FROM dual
) e
ON (b.employee_id = e.employee_id)
etc
0
 
flow01Commented:
Where should the data come from that you want to insert ?
0
 
MaarvaadiAuthor Commented:
Ah.. I am going to use this statement in java code, so I will have the data I need populated appropriately. My logic in java can be select first if present then update else insert. But if there is a single sql oracle can use then that would be better.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
MaarvaadiAuthor Commented:
Well the above you are operating on 2 tables. I dont have 2 tables. So I have a user table. I have ids of the user. I am supposed to update the dept of the user.

So I check the user table with the id, if the user is present then I update the dept else I insert a new user.

0
 
flow01Commented:
Please show the statement you would use to insert a new user.
0
 
mohammadzahidCommented:
Yes, you can use 1 table for MERGE using self-join on table. Use a correlation between 2 columns and if update then UPDATE or ELSE whatever.....

I am assuming you are trying to manage data in 1 table based on the events triggered by Java application code? Is this correct?

0
 
MaarvaadiAuthor Commented:
0
 
mohammadzahidCommented:
Yes, that is one way of doing it. Glad that you were able to resolve this issue.
0
 
MaarvaadiAuthor Commented:
Interesting. what is is the otherway of doing it? Is it more efficient than the above?
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.