Обсуждение: returning results from an update for joining other tables
Hi! I have a table containing data and a column which holds information on which compute-node processes the data. In a given interval I'd like to request some data from this table and mark these returned rows by setting the "process_node" column to the node-name, which asked for data. There may also be rows which have the column process_node set to the name of the node currently asking. What I tried was something like this, which gave me a syntax error: SELECT * FROM ( UPDATE taSET process_node='nodename'WHERE a>10 AND process_node is nullRETURNING * ) AS ta JOIN someothertable ON ... Can I somehow select some rows and do multiple operations on exactly this resultset? In my case update columns, then join columns from other tables and then return the resultset with the joined columns? Regards Patrick
am Tue, dem 10.06.2008, um 10:50:52 +0200 mailte Patrick Scharrenberg folgendes: > Hi! > > I have a table containing data and a column which holds information on > which compute-node processes the data. In a given interval I'd like to > request some data from this table and mark these returned rows by > setting the "process_node" column to the node-name, which asked for data. > There may also be rows which have the column process_node set to the > name of the node currently asking. > > What I tried was something like this, which gave me a syntax error: > > SELECT * FROM > ( UPDATE ta > SET process_node='nodename' > WHERE a>10 AND process_node is null > RETURNING * > ) AS ta > JOIN someothertable ON ... It's a know limitation, see <48468922.9030108@enterprisedb.com> Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
Hi! >> What I tried was something like this, which gave me a syntax error: >> >> SELECT * FROM >> ( UPDATE ta >> SET process_node='nodename' >> WHERE a>10 AND process_node is null >> RETURNING * >> ) AS ta >> JOIN someothertable ON ... > > It's a know limitation, see <48468922.9030108@enterprisedb.com> Oh, I see. Are there ways to work around this limitation? Patrick
Hi! >> >> What I tried was something like this, which gave me a syntax error: >> >> >> >> SELECT * FROM >> >> ( UPDATE ta >> >> SET process_node='nodename' >> >> WHERE a>10 AND process_node is null >> >> RETURNING * >> >> ) AS ta >> >> JOIN someothertable ON ... > > > > It's a know limitation, see <48468922.9030108@enterprisedb.com> Oh, I see. Are there ways to work around this limitation? Patrick