How to apply Update if an item exists and Insert otherwise

Question

How to create a procedure that goes from the top of the table and compares the value to null - If a match is found, insert an element in this position. - If not, the element is inserted into a new row

I need to correct a second row which contains null values in 4 last columns regardless of values in the Id and PropertyId columns

Here is a screenshot of my DB

enter image description here

Here is a samples of data:

enter image description here

Now it works so, which is not suitable to me, instead it should update the row with null values like on the last screenshot

enter image description here

But the next entry should overwrite the value of NULL for Item, ItemId, InstanceId and Instance

enter image description here

Can you give an example of what you are trying to do? Why do you need a cursor? — Mark Byers, Oct 24 '11 at 09:12
Please give table structures, example data and explain what "manufacture of an element in this position" means? Sounds like something that doesn't need a cursor and can be done in a set based way (possibly using `Merge` for 2008) — Martin Smith, Oct 24 '11 at 09:12
@revolutionkpi: your update did not answer Martin's questions, though. — Erwin Brandstetter, Oct 24 '11 at 09:20
@leppie, no, I am trying to do this task with different ways — revolutionkpi, Oct 24 '11 at 09:36
Could you then please give some more context ? I have a hard time figuring out why you'd want to do that operation in that specific way. — Stephane, Oct 24 '11 at 09:43
What is the source of information for the update? Where do you get the values from? — Mikael Eriksson, Oct 24 '11 at 13:43
@Mikael Eriksson, the source of information for the update came from c# method — revolutionkpi, Oct 24 '11 at 13:49
@Mikael Eriksson, it shoud check: if there is a null fild for example in Item, it should write the next item in first cell with null value in this column if there is not null item in this column it should insert valu on the next cell — revolutionkpi, Oct 24 '11 at 14:02

score 1 · Answer 1 · answered Oct 24 '11 at 13:53

1

Write a stored procedure like:

create procedure INSERT_OR_UPDATE as
begin
  if exists ( select * from Numerations where <your condition> )
    begin
      update Numerations set < ... > where < ... >
    end
  else
    begin
      insert into Numerations values <...>
    end
end

You have to check the syntax because I cannot test my code right now.

answered Oct 24 '11 at 13:53

Nicola Musatti

17,834
2
46
55

@ Nicola Musatti, so for example for Property column it should be: create procedure INSERT_OR_UPDATE as begin if exists ( select * from Numerations where Property=null ) begin update Numerations set Property where Property=Property end else begin insert into Numerations values @Property end end – revolutionkpi Oct 24 '11 at 14:21
The stored procedure in my example only executes one statement, either an insert or an update. If you need different statements for each row in your table you probably need to write a stored procedure that uses a cursor. – Nicola Musatti Oct 24 '11 at 14:27
so, do you know how to write and can you help me with a stored procedure that uses a cursor? – revolutionkpi Oct 24 '11 at 14:31
or it can be realised with 3 different stored procedure? – revolutionkpi Oct 24 '11 at 14:37

How to apply Update if an item exists and Insert otherwise

1 Answers1

Linked