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.

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]

Brilliant thanks Fernando!

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

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