Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 5 of 5 total |
UPDATE JOIN |
Thu, Feb 2 2012 7:26 PM | Permanent Link |
IQA | Hi Guys and Gals,
I'm sure this is fairly simple, but I've not had much luck. Looking to do an update based on a JOIN. I have 2 tables (trans and items) and want to set a BOOLEAN field in trans to true if a field in item is equal to 30. So something along the lines of... UPDATE trans INNER JOIN items ON trans.ref = items.ref SET trans.eodflag = true WHERE item.status = 30 I hope that makes sense, Thanks, Phil. |
Thu, Feb 2 2012 10:39 PM | Permanent Link |
Fernando Dias Team Elevate | Ohil,
That syntax in not allowed for update statements in EDB. Instead of it you may use this: UPDATE trans t SET eodflag = true WHERE EXISTS(SELECT status FROM items i WHERE i.Ref = t.Ref AND status = 30) -- Fernando Dias [Team Elevate] |
Mon, Feb 6 2012 10:31 PM | Permanent Link |
IQA | Brilliant thanks Fernando!
|
Mon, Feb 6 2012 10:34 PM | Permanent Link |
IQA | Hi Fernando,
I do have another UPDATE / SELECT to work out, with a slight variation. This one is trying to update stocktotal based on the qty used in the trans table and those 2 tables are joined / referenced by the field itemID UPDATE items SET stocktotal = stocktotal - ( SELECT trans.qty FROM trans JOIN items i ON i.itemID = trans.itemID WHERE i.stocktype = true ) I try the above but get (A scalar query can only return a single value) Any ideas? Thanks |
Tue, Feb 7 2012 3:37 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Philip
As a guess I would expect there to be multiple records in the transactions table for each itemID. What you're doing is effectively saying SET stocktotal = stocktotal - (47 33 28 etc) No guarantees but try UPDATE items SET stocktotal = stocktotal - ( SELECT SUM(trans.qty) FROM trans JOIN items i ON i.itemID = trans.itemID WHERE i.stocktype = true ) Roy Lambert |
This web page was last updated on Tuesday, September 17, 2024 at 04:19 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |