Dan, I don't see "INSERT INTO" the master table. I don't think you can do an update unless the row is already inserted.
Jan -----Original Message----- From: "Dan Goldberg" <[email protected]> To: [email protected] (RBASE-L Mailing List) Date: Wed, 22 Jan 2014 10:24:11 -0800 Subject: [RBASE-L] - Updating Mystery Using the latest update 9.5 64 I have a series of commands that I think should work but does not. --create a temporary view to get totals create temp view tmppo (partnum,qty_rec) as select partnum,sum(qty_rec) from po_rec_log where rec_date = 1/14/2014 group by partnum --update master table to add to qoh update master set qoh = (t1.qoh + t2.qty_rec) from master t1, tmppo t2 where t1.partnum = t2.partnum It says it updated the correct amount of rows but does not add to the qoh. I can see the rows in the view so I know the view is working. But if I put the view’s data into a temporary table and update, it works. --create a temporary view to get totals create temp view tmppo (partnum,qty_rec) as select partnum,sum(qty_rec) from po_rec_log where rec_date = 1/14/2014 group by partnum --create a temporary table to hold the view create temp table tmppo2 (partnum text 25,qty_rec real) --insert the view into the temp table insert into tmppo2 select all from tmppo --update master table to add to qoh update master set qoh = (t1.qoh + t2.qty_rec) from master t1, tmppo2 t2 where t1.partnum = t2.partnum Then it works. Shouldn’t it work off of the view? Dan Goldberg
