将临时表中的一列中的数据检查到另一个表中的列(Checking data in one column in a temp table to a column in another table)

我在临时表中有一个列，每行包含不同的值。 我想检查以确保该列中的值存在于不同表中的任何行中。 如果他们不这样做，则调用RaisError。 如果临时表中只有一行，我可以这样做。 但是，如果有两个或更多，我得到：

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= ,

我的Tsql是：

IF ((SELECT tmp.[SomeRefNumber] FROM dbo.TmpTable as tmp) NOT IN (SELECT Source.SameRefDiffTable FROM SOURCETABLE)) BEGIN RAISERROR('Oh no reference doesn't exist ', 16, 1) WITH NOWAIT END

我正在阅读正在发生的事情，但我发现的解决方案似乎有点矫枉过正。

I have a column in a temp table, that contains different values for each row. I want to check to make sure the values in that column exist in any row in a different table. If they do not, RaisError is called. I can do this if there is only 1 row in the temp table. However, if there is two or more, I am getting:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= ,

My Tsql is:

IF ((SELECT tmp.[SomeRefNumber] FROM dbo.TmpTable as tmp) NOT IN (SELECT Source.SameRefDiffTable FROM SOURCETABLE)) BEGIN RAISERROR('Oh no reference doesn't exist ', 16, 1) WITH NOWAIT END

I was reading on what is going on, but the solutions I have found seem to be overkill.

最满意答案

刚注意到我的查询很糟糕..问题解决了。

IF EXISTS (SELECT tmp.[SomeRefNumber] FROM dbo.TmpTable as tmp where tmp.[SomeRefNumber] NOT IN (SELECT SOURCE.SameRefDiffTable FROM SOURCE)) BEGIN RAISERROR('Oh no reference doesn't exist ', 16, 1) WITH NOWAIT END

Just noticed my query was bad.. problem solved.

IF EXISTS (SELECT tmp.[SomeRefNumber] FROM dbo.TmpTable as tmp where tmp.[SomeRefNumber] NOT IN (SELECT SOURCE.SameRefDiffTable FROM SOURCE)) BEGIN RAISERROR('Oh no reference doesn't exist ', 16, 1) WITH NOWAIT END将临时表中的一列中的数据检查到另一个表中的列(Checking data in one column in a temp table to a column in another table)

我在临时表中有一个列，每行包含不同的值。 我想检查以确保该列中的值存在于不同表中的任何行中。 如果他们不这样做，则调用RaisError。 如果临时表中只有一行，我可以这样做。 但是，如果有两个或更多，我得到：

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= ,

我的Tsql是：

IF ((SELECT tmp.[SomeRefNumber] FROM dbo.TmpTable as tmp) NOT IN (SELECT Source.SameRefDiffTable FROM SOURCETABLE)) BEGIN RAISERROR('Oh no reference doesn't exist ', 16, 1) WITH NOWAIT END

我正在阅读正在发生的事情，但我发现的解决方案似乎有点矫枉过正。

I have a column in a temp table, that contains different values for each row. I want to check to make sure the values in that column exist in any row in a different table. If they do not, RaisError is called. I can do this if there is only 1 row in the temp table. However, if there is two or more, I am getting:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= ,

My Tsql is:

IF ((SELECT tmp.[SomeRefNumber] FROM dbo.TmpTable as tmp) NOT IN (SELECT Source.SameRefDiffTable FROM SOURCETABLE)) BEGIN RAISERROR('Oh no reference doesn't exist ', 16, 1) WITH NOWAIT END

I was reading on what is going on, but the solutions I have found seem to be overkill.

最满意答案

刚注意到我的查询很糟糕..问题解决了。

IF EXISTS (SELECT tmp.[SomeRefNumber] FROM dbo.TmpTable as tmp where tmp.[SomeRefNumber] NOT IN (SELECT SOURCE.SameRefDiffTable FROM SOURCE)) BEGIN RAISERROR('Oh no reference doesn't exist ', 16, 1) WITH NOWAIT END

Just noticed my query was bad.. problem solved.

IF EXISTS (SELECT tmp.[SomeRefNumber] FROM dbo.TmpTable as tmp where tmp.[SomeRefNumber] NOT IN (SELECT SOURCE.SameRefDiffTable FROM SOURCE)) BEGIN RAISERROR('Oh no reference doesn't exist ', 16, 1) WITH NOWAIT END