Received: from outthere.thinx.ch (outthere.thinx.ch [194.158.233.34]) by hub.org (8.9.3/8.9.3) with ESMTP id EAA56971 for ; Mon, 28 Jun 1999 04:39:05 -0400 (EDT) (envelope-from Christian.Rudow@thinx.ch) Received: from iraeugst.net.thinx.ch (root@[212.28.159.117]) by outthere.thinx.ch (8.9.2/8.9.2) with ESMTP id KAA24613 for ; Mon, 28 Jun 1999 10:37:33 +0200 (CEST) Received: from thinx.ch (chris@acer.net.thinx.ch [192.168.0.33]) by iraeugst.net.thinx.ch (8.8.8/8.8.8) with ESMTP id JAA00794 for ; Mon, 28 Jun 1999 09:21:43 +0200 Sender: chris@iraeugst.net.thinx.ch Message-ID: <377731EB.AACAEA8B@thinx.ch> Date: Mon, 28 Jun 1999 10:27:23 +0200 From: Christian Rudow Organization: ThinX X-Mailer: Mozilla 4.5 [en] (X11; I; Linux 2.0.36 i686) X-Accept-Language: en MIME-Version: 1.0 To: PostgreSQL SQL Subject: Using Update Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit I have the following SQL understanding problem concerning UPDATE : I have two tables I want to join over a new artificial key a_id. The tables can already be joined over an existing foreign key. select a.a_id from a,b where a.custno = b.custno => selects about 1000 id's b.a_id so far only holds NULL values and should be updated to hold the correct foreign key references to a.a_id. +-------------------+ | a | |-------------------| | a_id prim. key |0---+ | custno altern.key| | +-------------------+ | | mc +----------------+ | b | +----------------+ | b_id prim.Key | | a_id forgn.Key| 1 custno | +----------------+ What i want to do is something like this : ------------------------------------------ update b set b.a_id = a.a_id where b.custno = a.custno; or is that the right way to do it ? : update b set b.a_id = (select a.a_id from a,b where a.custno = b.custno) where b.custno in (select a.custno from a); I just seem to use the UPDATE not correctly, it cannot be parsed. What is the correct solution to this ? Can anyone help ? I know that's a RTFM but I have no FM to R. Is there a good SQL tutorial/reference accessible over the net ? ThanX -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Christian Rudow E-Mail: Christian.Rudow@thinx.ch ThinX networked business services Stahlrain 10, CH-5200 Brugg ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~