CODEWITHSUNDEEP
c#sql-serverif-statementexistsinsert-update
Murat SK
Murat SK

Reputation: 31

How to use IF EXISTS in SQL (ifexists update, else insert in SQL)

string searched = TextBox1.Text; // for example, text is 4477

...

sorgu.CommandText = "IF EXISTS(SELECT * FROM [Pins] WHERE Pin =' " + searched.ToString() + " ') BEGIN UPDATE [Pins] SET SAY= SAY+1, Pin = ' " + searched.ToString() + " ' END ELSE BEGIN INSERT INTO Pins(SAY,Pin) VALUES (+1,' " + searched.ToString() + " ') END";

...

I am using SAY for counting the number of searches.

This code is changing all records on column (Pins) to searched text.

Where/What is my fault?

Upvotes: 0

Views: 4365

Answers (3)

Pradeep Kumar
Pradeep Kumar

Reputation: 6979

You missed the WHERE clause.

I would also prefer using Parameters instead of string concatenated query (which makes your code open to SQL injection attacks)

string searched = TextBox1.Text; // for example, text is 4477

sorgu.CommandText = "IF EXISTS(SELECT * FROM [Pins] WHERE Pin = @searched) BEGIN UPDATE [Pins] SET SAY=SAY+1 WHERE Pin = @searched END ELSE BEGIN INSERT INTO Pins(SAY,Pin) VALUES (1, @searched) END";
sorgu.Parameters.AddWithValue("@searched", TextBox1.Text);

Upvotes: 0

Bradley
Bradley

Reputation: 76

You need to have a where to not update all records. This would fix it.... but is a horrible query. 

IF EXISTS(SELECT * FROM [Pins] WHERE Pin = 'searched.ToString()')
BEGIN 
    UPDATE [Pins] SET SAY = SAY + 1
    WHERE [Pin] = 'searched.ToString() '
END 
ELSE 
BEGIN 
    INSERT INTO Pins(SAY, Pin) VALUES (1, 'searched.ToString()') 
END

Upvotes: 1

Tab Alleman
Tab Alleman

Reputation: 31795

In pseudo code, you're saying

IF EXISTS(SELECT Statement) UPDATE ALL ROWS

The correct way to do this with EXISTS is

UPDATE TABLE
WHERE EXISTS(Correlated SELECT Statement)

Upvotes: 1

Related Questions