使用SQL子查询错误更新多个记录时出错(Error updating Multiple records with SQL subquery error)

使用SQL子查询错误更新多个记录时出错(Error updating Multiple records with SQL subquery error)

我来自C＃linq背景所以我试图学习SQL。 我想要一个简单的子查询，它将使用与[User]表中的ContactID相对应的新UserID更新我的[AccountContact]表中的多个记录。 我不想使用连接。

我的代码如下

UPDATE [dbo].[AccountContact] SET UserID = (SELECT UserID from [User] WHERE ContactID IS NOT NULL), ContactID = null GO

在此先感谢通过运行此代码我得到错误

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

I come from a C# linq background so im trying to learn SQL. I would like a simple subquery that will update multiple records in my [AccountContact] table with new UserID's that correspond to the ContactID in the [User] Table. i do not want to use joins.

My code is as follows

UPDATE [dbo].[AccountContact] SET UserID = (SELECT UserID from [User] WHERE ContactID IS NOT NULL), ContactID = null GO

Thanks in Advance By running this code i get the error

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

最满意答案

我想你需要这个

UPDATE [dbo].[AccountContact] SET UserID = 'Whatever value you want', ContactID = null where UserID = (SELECT UserID from [User] WHERE ContactID IS NOT NULL)

I have found the solution to this problem

I have edited my code as follows

UPDATE [AccountContact] SET UserID = ISNULL((SELECT UserID FROM [User] WHERE [User].[ContactID] = [AccountContact].[ContactID]),[AccountContact].UserID), ContactID = NULL WHERE [AccountContact].[ContactID] IS NOT NULL

This does exactly what i need it to.