我来自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 GOThanks 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 NULLThis does exactly what i need it to.
更多推荐
发布评论