| Author |
 Topic  |
|
|
uxphreak
Starting Member
38 Posts |
 Posted - 01/13/2005 : 10:44:53
|
Good morning,
I'm having this really odd problem with updating one table with data from another table where both tables are in the same database. I have a similar update query that does work, however, because of some db changes, I needed to modify the query accordingly.
I get the following errors:
Server: Msg 8180, Level 16, State 1, Line 3
Statement(s) could not be prepared.
Server: Msg 209, Level 16, State 1, Line 3
Ambiguous column name 'StatusID'.
Here's the update query:
-- Update POS Product Table from SyteLine
UPDATE
[dev-svr1].nambeposnew.dbo.ProductLocationTemp
SET
LocationPrice = pt.BasePrice,
StatusID = pt.StatusID
/*
SELECT
pt.SkuID,
pt.BasePrice,
plt.LocID,
plt.LocationPrice,
pt.StatusID AS ProdStatus,
plt.StatusID AS PLTStatus
*/
FROM
[dev-svr1].nambeposnew.dbo.ProductTemp pt
INNER JOIN
[dev-svr1].nambeposnew.dbo.ProductLocationTemp plt
ON
pt.SkuID = plt.SkuID
WHERE
plt.LocID IN ('1','2','3','4','6','7','8','c')
AND
(
pt.BasePrice != plt.LocationPrice
OR
pt.StatusID != plt.StatusID
)
Thanks,
D |
|
|
Chester
Starting Member
USA
27 Posts |
Posted - 01/13/2005 : 14:00:15
|
Your getting the Ambiguous column name 'StatusID' error because
your set clause has this line: StatusID = pt.StatusID |
 |
|
|
uxphreak
Starting Member
38 Posts |
Posted - 01/13/2005 : 14:01:56
|
Even if I change my Update Query, I get the errors. Here's my updated query:
UPDATE
plt
SET
plt.LocationPrice = pt.BasePrice,
plt.StatusID = pt.StatusID
FROM
[dev-svr1].nambeposnew.dbo.ProductLocation plt
INNER JOIN
[dev-svr1].nambeposnew.dbo.Product pt
ON
pt.skuID = plt.skuID
WHERE
plt.LocID IN ('1','2','3','4','6','7','8','c')
AND
(
pt.BasePrice <> plt.LocationPrice
OR
pt.StatusID <> plt.StatusID
) |
|
|
|
Chester
Starting Member
USA
27 Posts |
Posted - 01/13/2005 : 14:52:43
|
| Hmm. I'm sorry, but I don't know how to fix this. |
|
|
|
jsmith8858
Dr. Cross Join
USA
7423 Posts |
Posted - 01/13/2005 : 15:05:52
|
you should not be getting that same error. make sure you are cutting and pasting the actual code you are trying, and also give us the new error message you are receiving.
- Jeff |
|
|
|
uxphreak
Starting Member
38 Posts |
Posted - 01/13/2005 : 15:37:17
|
Hi jsmith8858,
The code I pasted is the same as what I ran which produced these results:
Server: Msg 8180, Level 16, State 1, Line 3
Statement(s) could not be prepared.
Server: Msg 209, Level 16, State 1, Line 3
Ambiguous column name 'StatusID'. |
|
|
|
jsmith8858
Dr. Cross Join
USA
7423 Posts |
Posted - 01/13/2005 : 15:46:55
|
paste in your actual query, the whole thing, right from query analzyer as well. if you are trying what you have previously posted, that error should not be occuring -- everything appears to be fully qualified correctly.
- Jeff |
|
|
|
uxphreak
Starting Member
38 Posts |
Posted - 01/13/2005 : 15:49:32
|
Here's the exact query I am running in QA:
-- Update POS ProductLocation Table from Product Table
UPDATE
plt
SET
plt.LocationPrice = pt.BasePrice,
plt.StatusID = pt.StatusID
/*
--
SELECT
pt.SkuID,
pt.BasePrice,
plt.LocID,
plt.LocationPrice,
pt.StatusID AS ProdStatus,
plt.StatusID AS PLTStatus
--
*/
FROM
[dev-svr1].nambeposnew.dbo.ProductLocation plt
INNER JOIN
[dev-svr1].nambeposnew.dbo.Product pt
ON
pt.skuID = plt.skuID
WHERE
plt.LocID IN ('1','2','3','4','6','7','8','c')
AND
(
pt.BasePrice <> plt.LocationPrice
OR
pt.StatusID <> plt.StatusID
)
And, here's the output when I run the query in QA:
Server: Msg 8180, Level 16, State 1, Line 3
Statement(s) could not be prepared.
Server: Msg 209, Level 16, State 1, Line 3
Ambiguous column name 'StatusID'. |
|
|
|
jsmith8858
Dr. Cross Join
USA
7423 Posts |
Posted - 01/13/2005 : 16:22:31
|
Try replacing:
SET
plt.LocationPrice = pt.BasePrice,
plt.StatusID = pt.StatusID
with
SET
LocationPrice = pt.BasePrice,
StatusID = pt.StatusID
Kind of the opposite of what the error is telling you, actually -- i think with SET, you cannot put a qualifier before a column name, since all sets must be from the same table and it is already indicated.
- Jeff |
|
|
|
uxphreak
Starting Member
38 Posts |
Posted - 01/13/2005 : 16:25:24
|
Did what you recommended and got the same errors... I tried this prior to posting.
D |
|
|
| |
Topic |
|
|
|
Update query is not working
