SqlCommand If exist - update , if not exist - insert into

Question

Hello I have got this following statement that I would like it to INSERT INTO if there wasn't found any value with id = 1

If it exist I would like to update

I inspired by this:

    UPDATE Table1 SET (...) WHERE Column1='SomeValue'
IF @@ROWCOUNT=0
    INSERT INTO Table1 VALUES (...)

But getting this Exception: Incorrect syntax near (.

But I cant find where do I make a mistake , would someone help me solve this out please?

 string sqlcom = "UPDATE firma SET (firma=@firma,ulice=@ulice,mesto=@mesto,psc=@psc,ico=@ico,dico=@dico,dph=@dph,sdph=@sdph,upo1=@upo1,raz1=@raz1) WHERE id='1' IF @@ROWCOUNT=0 INSERT INTO firma (firma,ulice,mesto,psc,ico,dico,dph,sdph,upo1,raz1) VALUES (@firma,@ulice,@mesto,@psc,@ico,@dico,@dph,@sdph,@upo1,@raz1)";

                SqlCommand prikaz =
                    new SqlCommand(sqlcom, spojeni);
                prikaz.Parameters.AddWithValue("@firma", ffirma.Text);
                prikaz.Parameters.AddWithValue("@ulice", fulice.Text);
                prikaz.Parameters.AddWithValue("@mesto", fmesto.Text);
                prikaz.Parameters.AddWithValue("@psc", fpsc.Text);
                prikaz.Parameters.AddWithValue("@ico", fico.Text);
                prikaz.Parameters.AddWithValue("@dico", fdico.Text);
                prikaz.Parameters.AddWithValue("@dph", fdph.Text);
                prikaz.Parameters.AddWithValue("@sdph", fsdph.Text);
                prikaz.Parameters.AddWithValue("@raz1", fraz1.Text);
                prikaz.Parameters.AddWithValue("@upo1", fupo1.Text);

                spojeni.Open();
                prikaz.ExecuteNonQuery();
                spojeni.Close();

Remove the ( and ) around the values in your SET block - i.e., SET firma=@firma,ulice=@ulice,.. — Tim
– Tim, Commented Sep 9, 2013 at 7:59

Steve · Accepted Answer · 2013-09-09 08:09:24Z

4

Remove the open parenthesis after SET

UPDATE Firma SET firma=@firma ....

And, of course, the closing one after the list of fields updated.

However, a semicolon before the IF should also be added to allow a distinction between the two statements

UPDATE ...... WHERE id='1'; IF @@ROWCOUNT ......

edited Sep 9, 2013 at 8:09

answered Sep 9, 2013 at 8:00

Steve

217k22 gold badges242 silver badges296 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

podiluska · Accepted Answer · 2013-09-09 07:56:57Z

2

Have you considered using the MERGE Sql Command?

That will allow you to do an update, if a record matches certain conditions, otherwise do an insert.

Something like

MERGE firma AS target
USING (SELECT @SomeValue, @firma, @ulice...) AS source (ID, Firma, Ulice)
ON (target.ID= source.ID)
WHEN MATCHED THEN 
    UPDATE SET 
      Firma = source.Firma,
      Ulice  = source.Ulice, 
      ...
WHEN NOT MATCHED THEN   
     INSERT (ID, Firma, Ulice, ...)
     VALUES (source.ID, source.Firma, source.Ulice, ....)

http://technet.microsoft.com/en-us/library/bb510625.aspx

answered Sep 9, 2013 at 7:56

podiluska

51.6k7 gold badges102 silver badges105 bronze badges

1 Comment

Steve Over a year ago

+1 but note that this command is available starting from Sql Server 2008 and I still wonder why the guys at MS made it so complex

David M · Accepted Answer · 2013-09-09 07:53:12Z

1

You need to separate your SQL statements. Try a semi-colon before your IF statement.

answered Sep 9, 2013 at 7:53

David M

73.2k13 gold badges164 silver badges187 bronze badges

1 Comment

Marek Over a year ago

Thanks for your time but still got the same Ex with using semi-colon isn't it because I probably misunderstood use of @@ROWCOUNT? Is it used correctly ?

AlexB · Accepted Answer · 2013-09-09 08:00:39Z

0

IMHO, you first need to correctly indent your SQL statement. It will be easier to see where is the mistake.

It is probably a parenthesis probelm. Try :

IF (@@ROWCOUNT=0) BEGIN ... END

answered Sep 9, 2013 at 8:00

AlexB

7,45412 gold badges62 silver badges76 bronze badges

Collectives™ on Stack Overflow

SqlCommand If exist - update , if not exist - insert into

4 Answers 4

Comments

1 Comment

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related