我如何更新 SQL 表逻辑 [英] how can i update SQL table logic

问题描述

我有一个表格结构为，

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,

更新 Crate ID of Fruit ID if Fruit ID 已经在表中，如果没有则在表3中插入记录作为新记录.

这就是我现在在代码中得到的，

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));        
}

推荐答案

您可以在 SQL Server 中使用 MERGE 语法执行更新插入":

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)

这篇关于我如何更新 SQL 表逻辑的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！