SQL Update from a Select

Question

Hey! 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 think the answer to your question is located here