Question

I want to update two fields of table (Store) from a select of others tables, but i don´t know how i can do it. The SQL system is in AS/400, so doesn´t have SQL Server or Oracle tricks :(

Here is the SELECT, i want the price and amount of an order (article table join for select only existing articles)

SELECT OrderDetails.Price, OrderDetails.Amount
FROM (OrderHeader JOIN OrderDetails ON OrderHeader.Number = OrderDetails.Number)
JOIN Articles ON Article.Reference = OrderDetails.Article
WHERE OrderHeader.Store =  074 
AND   OrderHeader.Date =  2009-12-04 
AND   OrderHeader.Number = 26485

And here is the UPDATE, i want update price and amount of each article from last SELECT

UPDATE Store
SET Store.Price = *****OrderDetails.Price*****
, Store.Amount = Store.Amount + *****OrderDetails.Amount*****
... ????

Thanks for the help, and excuse my Tarzan s english ;)

Answer 1

If you have the drivers, you can perform this update via a Linked Server Query, i.e. SQL Server can add the AS/400 as a linked server and perform the update on the file, we have an AS400 with DB2, we routinely do update via SQL Server Stored Procedures, but you have to do a Select First and then run your update (this is vendor specific - IBM AS/400 w/DB2 and SQL 05)

Declare @tmpSql nvarchar(1000);
Declare @baseSql nvarchar(1000);

-- Select 
Set @tmpsql =    Select * From MyAs400Library.file1 where Field1=      + @somevariable +       and Field2= + @someothervariable +     

Set @baseSql =  Update OpenQuery(LINKEDSERVERNAME,  + @tmpSql +  ) 

Set @baseSql = @baseSql +   SET Field3=   + @somevariable +     where Field1=    + @somevariable +     and Field2= + @someothervariable +   

exec sp_executesql @baseSql

So basically you re doing a SELECT and then an update...

Don t know if a Linked server is an option for you but this is one way.

Answer 2

I believe this should work:

UPDATE Store as ST (Price, Amount) = (SELECT OD.Price, ST.Amount + OD.Amount
                                      FROM OrderHeader as OH
                                      JOIN OrderDetails as OD
                                      ON OH.Number = OD.Number
                                      JOIN Articles as A
                                      ON A.Reference = OD.Article
                                      WHERE OH.Store = ST.Store
                                      AND OH.Date =  2009-12-04 
                                      AND OH.Number = 26485)
WHERE ST.Store =  074 
      AND EXISTS (SELECT  1 
                  FROM OrderHeader as OH
                  JOIN OrderDetails as OD
                  ON OH.Number = OD.Number
                  JOIN Articles as A
                  ON A.Reference = OD.Article
                  WHERE OH.Store = ST.Store
                  AND OH.Date =  2009-12-04 
                  AND OH.Number = 26485)

The WHERE EXISTS is to prevent against NULL results. I m assuming Store has an id column to match.
This will only work if the tables will return one (and only one) row for the given selection criteria. If this is not the case, you will need to supply more details.

友情链接