how can i update SQL table logic

Question

I have a table structured as,

Table 3

Fruit ID -  Foreign Key  (Primary Key of Table 1)
Crate ID -  Foreign Key  (Primary Key of Table 2)

Now I need to execute a query which will,

Update Crate ID of Fruit ID if Fruit ID is already in Table, and if not then insert record in table 3 as new record.

This is what I got in code right now,

private void RelateFuirtWithCrates(List<string> selectedFruitIDs, int selectedCrateID)
{

   string insertStatement = "INSERT INTO Fruit_Crate(FruitID, CrateID) Values " +
        "(@FruitID, @CrateID);";  ?? I don't think if it's right query

        using (SqlConnection connection = new SqlConnection(ConnectionString()))
        using (SqlCommand cmd = new SqlCommand(insertStatement, connection))
        {
            connection.Open();
            cmd.Parameters.Add(new SqlParameter("@FruitID", ????? Not sure what goes in here));
            cmd.Parameters.Add(new SqlParameter("@CrateID",selectedCrateID));        
}

possible duplicate of [How can I update Crate IDs of List of Fruits in single SQL query in c#](http://stackoverflow.com/questions/16539948/how-can-i-update-crate-ids-of-list-of-fruits-in-single-sql-query-in-c-sharp) — Jim G., May 21 '13 at 11:22

Marc Gravell · Accepted Answer · 2013-05-14T09:27:48.180

3

You can do an "upsert" with the MERGE syntax in SQL Server:

MERGE [SomeTable] AS target
USING (SELECT @FruitID, @CrateID) AS source (FruitID, CrateID)
ON (target.FruitID = source.FruitID)
WHEN MATCHED THEN 
    UPDATE SET CrateID = source.CrateID
WHEN NOT MATCHED THEN   
    INSERT (FruitID, CrateID)
    VALUES (source.FruitID, source.CrateID);

Otherwise, you can use something like:

update [SomeTable] set CrateID = @CrateID where FruitID = @FruitID
if @@rowcount = 0
    insert [SomeTable] (FruitID, CrateID) values (@FruitID, @CrateID)

edited May 14 '13 at 09:27

answered May 14 '13 at 09:21

Marc Gravell

1,026,079
266
2,566
2,900

@user13814 the `MERGE` syntax works well with lists, but: in terms of parameters that isn't easy. You need to look at either "table value parameters", or using some kind of "split" udf. Or just add lots of parameters. – Marc Gravell May 14 '13 at 09:25
Thanks, but I am still confused with how can I add FruitIDs as sql parameter sir, so asked another question, http://stackoverflow.com/questions/16539948/how-can-i-update-crate-ids-of-list-of-fruits-in-single-sql-query-in-c-sharp – Mathematics May 14 '13 at 09:48
@user13814 that is actually a surprisingly difficult thing to do; I've added an example using "dapper" to one of your later questions - we specifically added some helper code to support this common, but vexing, scenario – Marc Gravell May 14 '13 at 11:46

how can i update SQL table logic

1 Answers1

Linked