How to write the IFEXISTS condition and update the record from the codebehind?

Question

I want to write a IFEXISTS condition and update the record from the code behind I don't know whether its possible or not...

if its possible then someone please tell me the syntax for that..

How to update the record..

I want to write something like this.. but what is correct?

public void UpdateModalitiesId(int? CaseId, int ModalitiesId)

    {
        string query = "if exists (select count(*) from ImageModality where ImageModality.Id='"+ ModalitiesId +"')
                               UPDATE ImageGroup set ImageModalityId='" + ModalitiesId + "' where BaseCaseId='" + CaseId +"' ;
        //string query = "UPDATE ImageGroup set ImageModalityId='" + ModalitiesId + "' where BaseCaseId='" + CaseId + "'";
        SqlHelper.ExecuteNonQuery(strConnectionString, CommandType.Text, query);
    }

thats not the matter, I can remove that, but for me imp. is query how to write that.. — Rocky, Aug 01 '11 at 05:02
As a side note, you should consider using `SqlParameters` to inject the variables into the SQL instead of string concatenation. That code is subject to SQL Injection attacks if `CaseId` and `ModalitiesId` are rendered unsafe. — Chad Levy, Aug 01 '11 at 05:17

Alex_L · Accepted Answer · 2011-08-01T08:51:47.023

First, the statement select count(*) will always return at least one record, so your condition will always return TRUE. So you need condition like

IF (Select count(*) FROM ...) > 0

Next, if you need to update the record using your condition, you can use the IF .. BEGIN .. END statement. It is not necessarily to use BEGIN .. END, You can use your UPDATE immediatelly after IF condition, but in future, if you will modify your code, you can avoid the logic errors.

So, your code will be like this:

public void UpdateModalitiesId(int? CaseId, int ModalitiesId)

    {
        string query = "if (select count(*) from ImageModality where ImageModality.Id='"+ ModalitiesId +"') > 0 BEGIN
                               UPDATE ImageGroup set ImageModalityId='" + ModalitiesId + "' where BaseCaseId='" + CaseId +"; END' ;
        SqlHelper.ExecuteNonQuery(strConnectionString, CommandType.Text, query);
    }

EDIT: And the correct code is

public void UpdateModalitiesId(int? CaseId, int ModalitiesId)

    {
        string query = "if EXISTS(select * from ImageModality where ImageModality.Id='"+ ModalitiesId +"') 0 BEGIN
                               UPDATE ImageGroup set ImageModalityId='" + ModalitiesId + "' where BaseCaseId='" + CaseId +"; END' ;
        SqlHelper.ExecuteNonQuery(strConnectionString, CommandType.Text, query);
    }

better still would be `IF EXISTS(SELECT *...` - using `COUNT(*)` *may* cause the database to actually count all rows, whereas `EXISTS(SELECT *...` should terminate as soon as it finds one matching row. — Damien_The_Unbeliever, Aug 01 '11 at 06:50
How to check existence http://stackoverflow.com/questions/3271455/whats-the-best-to-check-if-item-exist-or-not-select-countidor-exist/3271464#3271464 — gbn, Aug 01 '11 at 08:08
I completelly agreed with you about EXIST and COUNT. In my solution I just fixed the error when EXISTS and COUNT used together. EXIST (SELECT COUNT (*)), as writed in the question, always returns TRUE, and I just fixed it. Ok, lets fix my answer... — Alex_L, Aug 01 '11 at 08:34

score 1 · Answer 2 · answered Aug 01 '11 at 08:12

Personally, I'd consider not testing first and just doing an UPDATE

Doing both requires 2 table touches for basically the same SELECT..WHERE...

So an UPDATE only will mean zero rows get updated if there are no rows. Aand the table is tiuched once

It's also possible that the row is INSERTED after EXISTS but before UPDATE under heavy load.

score 0 · Answer 3 · answered Aug 01 '11 at 05:13

0

You can check out something on these lines

IF EXISTS(SELECT count(*) from ImageModality where ImageModality.Id= ID > x)
BEGIN     
     UPDATE ImageGroup set ImageModalityId ....
END

answered Aug 01 '11 at 05:13

V4Vendetta

37,194
9
78
82

How to write the IFEXISTS condition and update the record from the codebehind?

3 Answers3