Update query is not working - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Update query is not working

Author

Topic

uxphreak
Starting Member

38 Posts

Posted - 2005-01-13 : 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

27 Posts

Posted - 2005-01-13 : 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 - 2005-01-13 : 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

27 Posts

Posted - 2005-01-13 : 14:52:43

Hmm. I'm sorry, but I don't know how to fix this.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-01-13 : 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 - 2005-01-13 : 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

7423 Posts

Posted - 2005-01-13 : 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 - 2005-01-13 : 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

7423 Posts

Posted - 2005-01-13 : 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 - 2005-01-13 : 16:25:24

Did what you recommended and got the same errors... I tried this prior to posting.

D

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources