CODEWITHSUNDEEP
c#asp.netsqlsql-server
John
John

Reputation: 2033

Update if exist in SQL Server table

How to update the full row if BatchNumber (in my case) exists?

In my table batch number is unique and I need to update the entire row or the quantity column if Batchnumber is exist

Please, help me to do this in a proper way

{
    var conn = new SqlConnection(GetConnectionString());
    var StrBuilder = new StringBuilder(string.Empty);
    var splitItems = (string[])null;

    foreach (string item in SC_PurLinr)
    {
        const string sqlStatement = "INSERT INTO DEL_Stores (DealerCode, Code, Qty, ExpireDate, BatchNumber) VALUES";

        if (item.Contains(","))
        {
            splitItems = item.Split(",".ToCharArray());
            StrBuilder.AppendFormat("{0}('{1}','{2}','{3}','{4}','{5}'); ", sqlStatement, splitItems[0], splitItems[1], splitItems[2], splitItems[3], splitItems[4]);
        }
    }

    try
    {
        conn.Open();
        SqlCommand cmd = new SqlCommand(StrBuilder.ToString(), conn) { CommandType = CommandType.Text };
        cmd.ExecuteNonQuery();
        wucMessagePopup1.showPopup(1, string.Empty, "Record Saved Successfully.");
    }
    catch (SqlException ex)
    {
    }
    finally
    {
        conn.Close();
    }
}

Upvotes: 0

Views: 1445

Answers (3)

pRoToss
pRoToss

Reputation: 31

Just add IF statement before insert data as below:

IF  EXISTS (select * from sys.columns where object_id = OBJECT_ID(N'DEL_Stores') and name='BatchNumber')
BEGIN
// Do insert here
END

Upvotes: 2

Sain Pradeep
Sain Pradeep

Reputation: 3125

You can achieve this by stored procedure in better way.

Your procedure should be like this.

create PROCEDURE usp_Namae
         @parmeter INT
    ,@parmeter INT
    ,@BatchNumber INT
AS
BEGIN
    IF NOT EXISTS (
            SELECT *
            FROM tblname
            WHERE BatchNumber = @BatchNumber
            )
    BEGIN
        --INSERT query
    END
    ELSE
    BEGIN
        --Update query
    END
END

Upvotes: 2

Ivandolchevic
Ivandolchevic

Reputation: 184

Use MERGE from SQL Server 2008:

WITH new_rows (
    SELECT @value value 
        ,@BatchNumber BatchNumber)
MERGE DEL_Stores target
USING new_rows source ON source.BatchNumber = target.BatchNumber
WHEN MATCHED THEN
    UPDATE SET value = @value
WHEN NOT MATCHED THEN
    INSERT (    
        BatchNumber
        ,value)
    VALUES(
        source.BatchNumber
        source.value);

Upvotes: 4

Related Questions