Update 1 table to another table with select

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 2005 Forums

Transact-SQL (2005)

Update 1 table to another table with select

Author

Topic

scripter
Starting Member

49 Posts

Posted - 2009-01-19 : 11:08:20

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
)

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-01-19 : 15:07:02

quote:
Originally posted by scripter

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
)

If you are updating the x_TESTTable it needs to be included in your FROM clause so something can correlate rows of that table with your other tables. Also, a WHERE EXISTS subquery needs to be correlated to the main query. It may be but since you didn't prefix the columns with table aliases and you didn't post any table structures I can't tell. You need to use table aliases consistantly throughout the statement. Either alias all tables or no tables. All columns should be prefixed with the table name or table alias to avoid ambiguity. Finally, it looks like your subquery should be JOINed as a derived table rather than in the context of a WHERE EXISTs clause s

EDIT:
lost the end of my last sentence:

since you are using the aggregate value as the new min_quantity in x_TESTTAble.

Be One with the Optimizer
TG

scripter
Starting Member

49 Posts

Posted - 2009-01-19 : 16:39:59

Ok I think the overall question on this was a bit lost in translation

so forgetting the SQL I provided above how do I update TableA with select results from TableB also in the select results of TableB I would sum a column up

I can update 1 row in a table from a select statement that only pulls 1 row.

I can update multiple rows
but I cannot update multiple rows if one of the columns was sum'd

anyone have any suggested SQL on that.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-01-19 : 22:04:49

I think the answer was lost in translation as well

take a look at this then re-read my original answer to see if it makes more sense.


update a set
   a.min_quantity = d.min_quantity
from   x_TESTTable a
inner join (
       select item_code
              ,min(i.item_qty) as min_quantity
       from   x_invoic i
       where i.status = 8 and i.DW_UPDATE_DATE > DATEADD(day, -120, CURRENT_TIMESTAMP)
       ) d
            on d.item_code = a.item_code

EDIT:
i forgot the GROUP BY in the derived table - you'll need that.

Be One with the Optimizer
TG

scripter
Starting Member

49 Posts

Posted - 2009-01-20 : 00:00:03

Thank you so much That did it for me

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources