• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 725
  • Last Modified:

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?
0
Maarvaadi
Asked:
Maarvaadi
  • 4
  • 3
  • 2
1 Solution
 
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
 
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
Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

 
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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 4
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now