February 26, 2019 at 1:15 am
I am trying to use the SQL below
UPDATE
WMWHSE1.LOTATTRIBUTE
SET LOTTABLE05 =
(
SELECT
I.LOTTABLE05
FROM WMWHSE1.ITRN I
JOIN WMWHSE1.LOTATTRIBUTE L
ON I.LOTTABLE06 = L.LOTTABLE06
WHERE
L.STORERKEY = '11871'
AND L.SKU = '243795'
)
Getting this error. Any suggestions?
Msg 512, Level 16, State 1, Line 2
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.
February 26, 2019 at 1:45 am
The error message tells you exactly what's wrong. You need to rewrite the subquery so it returns only one value.
John
February 26, 2019 at 1:56 am
The answer is in the error message, your subquery needs to return only ONE value, ooops didn't see Johns response!
...
February 26, 2019 at 7:10 am
DOUCETTR26 - Tuesday, February 26, 2019 1:15 AMI am trying to use the SQL belowUPDATE
WMWHSE1.LOTATTRIBUTE
SET LOTTABLE05 =
(
SELECT
I.LOTTABLE05
FROM WMWHSE1.ITRN I
JOIN WMWHSE1.LOTATTRIBUTE L
ON I.LOTTABLE06 = L.LOTTABLE06
WHERE
L.STORERKEY = '11871'
AND L.SKU = '243795'
)
Getting this error. Any suggestions?
Msg 512, Level 16, State 1, Line 2
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.
The following query is returning more than one record
SELECT
I.LOTTABLE05
FROM WMWHSE1.ITRN I
JOIN WMWHSE1.LOTATTRIBUTE L
ON I.LOTTABLE06 = L.LOTTABLE06
WHERE
L.STORERKEY = '11871'
AND L.SKU = '243795'
)
February 26, 2019 at 8:52 am
I would write this more compactlyUPDATE WMWHSE1.LOTATTRIBUTE
SET LOTTABLE05 = I.LOTTABLE05
FROM WMWHSE1.ITRN I
JOIN WMWHSE1.LOTATTRIBUTE L
ON I.LOTTABLE06 = L.LOTTABLE06
WHERE
L.STORERKEY = '11871'
AND L.SKU = '243795'
The error is what people posted above. SQL Server is getting multiple results back and can't decide which goes into a single cell.
February 26, 2019 at 9:36 am
I don't think that you want to be using a sub-query at all. Try the following.
UPDATE L
SET LOTTABLE05 = I.LOTTABLE05
FROM WMWHSE1.ITRN I
JOIN WMWHSE1.LOTATTRIBUTE L
ON I.LOTTABLE06 = L.LOTTABLE06
WHERE L.STORERKEY = '11871'
AND L.SKU = '243795'
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply