如何更新SQL表的逻辑 [英] how can i update SQL table logic
本文介绍了如何更新SQL表的逻辑的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我已经构建成表,
表3
水果ID - 外键(表1)
木箱ID的主键 - 外键(表2)
的主键
现在我需要执行一个查询这将
更新 箱子ID
< STRONG>的 水果ID
如果 水果ID
已经表,如果没有的话插入记录表3为新的记录。
这是我在代码中得到了现在,
私人无效RelateFuirtWithCrates(列表<串> selectedFruitIDs,INT selectedCrateID)
{
串insertStatement =INSERT INTO Fruit_Crate(FruitID,CrateID)值+
(@FruitID,@CrateID);; ??我不认为,如果使用是正确的查询
(SqlConnection的连接=新的SqlConnection(的ConnectionString()))使用
(的SqlCommand CMD =新的SqlCommand(insertStatement,连接))
{
connection.Open();
cmd.Parameters.Add(新的SqlParameter(@ FruitID?????不知道什么放在这里));
cmd.Parameters.Add(新的SqlParameter(@ CrateID,selectedCrateID));
}
解决方案
您可以做一个UPSERT 与合并
语法在SQL Server:
合并[SomeTable] AS目标
使用(选择@FruitID,@CrateID)源(FruitID,CrateID)
ON(target.FruitID = source.FruitID)
当然后再配
更新集CrateID = source.CrateID
WHEN NOT然后再配
INSERT(FruitID,CrateID)
值(source.FruitID,source.CrateID);
否则,您可以使用类似:
更新[SomeTable]设置CrateID = @CrateID,其中FruitID = @FruitID
如果@@ ROWCOUNT = 0
插入[SomeTable(FruitID,CrateID)值( @FruitID,@CrateID)
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));
}
解决方案
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屋!
查看全文