如果id（对于每个sql行）存在则更新else插入

I have two tables. In first table i have columns id,name, I want to check in the second table if id(from first table) exists, then update name. If id doesnt exist insert id,name.

我有两张桌子。在第一个表中我有列id,名称,如果id(来自第一个表)存在,我想检查第二个表,然后更新名称。如果id不存在,则插入id,name。

I'm using this code but it doesnt work.

我正在使用此代码,但它不起作用。

System.Data.SqlClient.SqlCommand CheckNone = new System.Data.SqlClient.SqlCommand("IF EXISTS(SELECT id from test) SELECT 1 ELSE SELECT 0", con);
con.Open();
var result = (int)CheckNone.ExecuteScalar();
if (result == 0)
{
    cmd = new SqlCommand(" insert into test(id,name) select id,name from Tamio.dbo.memberform", con);
    cmd.ExecuteNonQuery();
    con.Close();
}
else
{
    SqlCommand cmd = new SqlCommand(" update test set test.name select memberform.name from Tamio.dbo.memberform", con);
    cmd.ExecuteNonQuery();
    con.Close();

I Want succeed this.

我希望成功。

2 个解决方案

#1

IF EXISTS(SELECT id from test) SELECT 1 ELSE SELECT 0

That will return 1 if any id exists

如果存在任何id,则返回1

The update does not match up rows in any way
Not even valid syntax

更新不以任何方式匹配行甚至没有有效的语法

update test set test.name select memberform.name from Tamio.dbo.memberform

syntax may be off as from memory but merge will do this

语法可能会从内存中关闭,但merge会执行此操作

MERGE 
   table2 AS target
USING 
   table1 AS source
ON 
   target.id = source.id 
WHEN MATCHED THEN 
   UPDATE SET target.name = source.name
WHEN NOT MATCHED THEN 
   INSERT (id, name) VALUES (source.id, source.name);

#2

-1

Why you use 3 query ? Make it one :

为什么使用3个查询?做一个:

IF EXISTS(SELECT id from test) UPDATE test SET *test.name (SELECT memberform.name FROM Tamio.dbo.memberform)*; ELSE INSERT INTO test(id,name) SELECT id,name FROM Tamio.dbo.memberform;

But your update code isnt valid. Probably its the cause of your problem :).

但是您的更新代码无效。可能是你的问题的原因:)。

#1

IF EXISTS(SELECT id from test) SELECT 1 ELSE SELECT 0