SQL query contains IF / ELSE statement

Question

Possible Duplicate:
Solutions for INSERT OR UPDATE on SQL Server

I am in a great trouble that I want write a SQL query that include a conditional statement.

EG : I have a SQL table with columns ID,NAME,STATUS so before I insert value in to this table I want to check the name that is already exists in the table. If it is exist then I want to update the status value.

Is it possible to write the query by using queryString (SQL Server query instead of stored procedure) method, If anybody knw pls help me. I am using SQL Server 2008 R2

Answer 1

You want the MERGE query syntax

From the documentation ( http://technet.microsoft.com/en-us/library/bb510625.aspx )

MERGE Production.UnitMeasure AS target
USING (SELECT @UnitMeasureCode, @Name) AS source (UnitMeasureCode, Name)
ON (target.UnitMeasureCode = source.UnitMeasureCode)
WHEN MATCHED THEN 
    UPDATE SET Name = source.Name
WHEN NOT MATCHED THEN   
    INSERT (UnitMeasureCode, Name)
    VALUES (source.UnitMeasureCode, source.Name)
    OUTPUT deleted.*, $action, inserted.* INTO #MyTempTable

Answer 2

Following are the possible ways:

1) Use 'EXISTS' method

IF EXISTS(SELECT 1 FROM Table1 WHERE Name = @Name)
    UPDATE Table1 SET Status = @Status WHERE Name = @Name
ELSE
    INSERT INTO Table1 (Name, Status) VALUES(@Name, @Status)

2) Use MERGE

MERGE Table1 AS TARGET
USING (SELECT @Name, @Status FROM Table1) AS Source (Name, Status)
ON (TARGET.Name = Source.Name)

WHEN MATCHED 
    THEN UPDATE SET Status = Source.Status
WHEN NOT MATCHED 
    THEN INSERT (Name, Status) VALUES (Source.Name, Source.Status)

3) Un-compiled Query (Building a query)

--Unable to post on S.O.F due to security limitations

Though the suggested methods are 1st & 2nd (3rd being unnecessary).

Answer 3

You want something like

IF EXISTS (SELECT Name FROM YourDatabase WHERE [_SomeCondition_])
BEGIN
    INSERT INTO YourTable (Status) 
    SELECT OtherStatus AS  Status 
    FROM SomeOtherTable 
END

That's it.

I hope this helps.