SQL Update from a Select
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 Arti开发者_如何学Gocles 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 ;)
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.
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.
精彩评论