SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Update query is not working
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

uxphreak
Starting Member

38 Posts

Posted - 01/13/2005 :  10:44:53  Show Profile  Reply with Quote
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  Show Profile  Reply with Quote
Your getting the Ambiguous column name 'StatusID' error because
your set clause has this line: StatusID = pt.StatusID
Go to Top of Page

uxphreak
Starting Member

38 Posts

Posted - 01/13/2005 :  14:01:56  Show Profile  Reply with Quote
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
)
Go to Top of Page

Chester
Starting Member

USA
27 Posts

Posted - 01/13/2005 :  14:52:43  Show Profile  Reply with Quote
Hmm. I'm sorry, but I don't know how to fix this.
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 01/13/2005 :  15:05:52  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
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
Go to Top of Page

uxphreak
Starting Member

38 Posts

Posted - 01/13/2005 :  15:37:17  Show Profile  Reply with Quote
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'.
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 01/13/2005 :  15:46:55  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
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
Go to Top of Page

uxphreak
Starting Member

38 Posts

Posted - 01/13/2005 :  15:49:32  Show Profile  Reply with Quote
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'.
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 01/13/2005 :  16:22:31  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
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
Go to Top of Page

uxphreak
Starting Member

38 Posts

Posted - 01/13/2005 :  16:25:24  Show Profile  Reply with Quote
Did what you recommended and got the same errors... I tried this prior to posting.

D
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000