SQL Update from a Select

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 ;)

How do you decide which value to use if a store has multiple orders against it and/or an order has multiple details? — Tom H
– Tom H, Commented Dec 4, 2009 at 18:45
Good point, Tom. If the select can be modified to guarantee only a single value returned, the answer can be a sub-query inside the udpate. — Tracy Probst
– Tracy Probst, Commented Dec 7, 2009 at 15:23

Ta01 · Accepted Answer · 2009-12-04 18:58:12Z

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.

Clockwork-Muse · Accepted Answer · 2014-06-12 08:34:11Z

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.

Collectives™ on Stack Overflow

SQL Update from a Select

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related