Link to home
Start Free TrialLog in
Avatar of Maarvaadi
Maarvaadi

asked on

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?
Avatar of flow01
flow01
Flag of Netherlands image

Where should the data come from that you want to insert ?
Avatar of Maarvaadi
Maarvaadi

ASKER

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.
ASKER CERTIFIED SOLUTION
Avatar of flow01
flow01
Flag of Netherlands image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.

Please show the statement you would use to insert a new user.
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?

Yes, that is one way of doing it. Glad that you were able to resolve this issue.
Interesting. what is is the otherway of doing it? Is it more efficient than the above?