LearnItFirst User Forum

Welcome Guest

Search | New Posts | Members | Log In | Register

SQL Server Forum - LearnItFirst.com » Transact-SQL Programming » DML (SELECT, INSERT, UPDATE, DELETE) Questions » Update 1 table to another table with select

Update 1 table to another table with select

Options

Previous Topic · Next Topic

scripter

Posted: Monday, January 19, 2009 10:14:57 AM

Rank: Newbie

Joined: 1/19/2009
Posts: 1
Points: 3

Below is the code I have I am trying to update the column MIN_QUANTITY in x_TESTTable with the value of the Alias MIN_QUANTITY in dbo.X_INVOIC there are over 500 records that are returned in the select statement which means there needs to be equal about of records updated I am lost currently the error I am getting is

Msg 209, Level 16, State 1, Line 2
Ambiguous column name 'MIN_QUANTITY'.

UPDATE x_TESTTable
SET MIN_QUANTITY = MIN_QUANTITY
FROM dbo.X_INVOIC a
LEFT JOIN ITEM_REPLENISH_DEPART b
ON a.ITEM_CODE = b.ITEM_CODE
WHERE
EXISTS
(
SELECT ITEM_CODE, SUM(ITEM_QTY) AS MIN_QUANTITY
FROM dbo.X_INVOIC
WHERE (STATUS = 8) AND DW_UPDATE_DATE > DATEADD(day, -120, CURRENT_TIMESTAMP)
GROUP BY ITEM_CODE
)

Scott Whigham

Posted: Thursday, January 22, 2009 8:37:39 AM

Rank: Super Mod

Joined: 3/20/2006
Posts: 476
Points: 1,053
Where do you live?: Dallas, TX

Hi there -

This video covers the idea of how to do an update with a FROM clause: http://learnitfirst.com/go.aspx?id=XVBK

Your problem is the line "SET MIN_QUANTITY = MIN_QUANTITY". That basically would say "SET me equal to my value" since you haven't specified what table MIN_QUANTITY (on the right side of the = sign) comes from. SET MIN_QUANTITY = b.MIN_QUANTITY might be what you want?

bklr

Posted: Tuesday, January 27, 2009 3:00:10 AM

Rank: PFY

Joined: 1/21/2009
Posts: 27
Points: 81
Where do you live?: India

u can use like this i think so
UPDATE x_TESTTable
SET MIN_QUANTITY = t.MIN_QUANTITY
FROM dbo.X_INVOIC a
INNER JOIN
(
SELECT ITEM_CODE, SUM(ITEM_QTY) AS MIN_QUANTITY
FROM dbo.X_INVOIC
WHERE (STATUS = 8) AND DW_UPDATE_DATE > DATEADD(day, -120, CURRENT_TIMESTAMP)
GROUP BY ITEM_CODE
)t ON a.ITEM_CODE = t.ITEM_CODE

Kokkula

Posted: Tuesday, January 27, 2009 7:00:30 AM

Rank: Newbie

Joined: 1/27/2009
Posts: 1
Points: 3
Where do you live?: India

Hello,

In the query it is not specified that with which value Min_Quantity value the value in that table should be set. SO it is throwing an error as ambiguous column name. Set the Alias_Name or Table_Name inform of the Min_Quantity Column_Name.

Hope helpful...

Thanks,
Pavan

bklr

Posted: Friday, January 30, 2009 3:28:35 AM

Rank: PFY

Joined: 1/21/2009
Posts: 27
Points: 81
Where do you live?: India

Kokkula wrote:

hai pavan,
it will not show an error it will update the min_quantity from x_TESTTable that table k check once
is it throwing error r not

Users browsing this topic

Guest

Forum Jump

You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.