X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id C7409D1B49E for ; Fri, 30 Jan 2004 08:20:30 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 11174-05 for ; Fri, 30 Jan 2004 04:20:32 -0400 (AST) Received: from itree.org (tree.caddev.com [24.153.136.6]) by svr1.postgresql.org (Postfix) with ESMTP id A4FFCD1CCDD for ; Fri, 30 Jan 2004 04:20:29 -0400 (AST) Received: from laptop (user-0cdfdln.cable.mindspring.com [24.215.182.183]) by itree.org (8.11.6/8.11.6) with SMTP id i0U8HBq21031 for ; Fri, 30 Jan 2004 02:17:11 -0600 From: "treeml" To: Subject: update more than 1 table (mysql to postgres) Date: Fri, 30 Jan 2004 03:19:32 -0500 Message-ID: MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook IMO, Build 9.0.2416 (9.0.2911.0) Importance: Normal X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165 X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200402/10 X-Sequence-Number: 16701 I am migrating from MySQL to Postagres. I have problem with postgres updating 2 tables with one statement. In MySQL I can update 2 tables (parent, child) with a statement like this UPDATE parent LEFT JOIN child ON parent.pid = child.foreign_key SET parent.field1 = 'company', child.field2 = 'john' WHERE child.pid = 7 Or I can also do UPDATE parent, child SET parent.field1 = 'company', child.field2 = 'john' WHERE parent.pid = child.foreign_key AND child.pid = 7 But I couldn't do that in Postgres, Only one table is allowed in an update statement. I tried to create a view, and updating the view, but that was not allowed. I could do 2 SQL updates, but I am sure there is a better way to do this. Anyone have any idea. Appreciated. Tree