Received: from outthere.thinx.ch (outthere.thinx.ch [194.158.233.34]) by hub.org (8.9.3/8.9.3) with ESMTP id DAA39426 for ; Mon, 28 Jun 1999 03:04:55 -0400 (EDT) (envelope-from Christian.Rudow@thinx.ch) Received: from iraeugst.net.thinx.ch (root@[212.28.159.17]) by outthere.thinx.ch (8.9.2/8.9.2) with ESMTP id JAA24501 for ; Mon, 28 Jun 1999 09:03:17 +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 IAA00441 for ; Mon, 28 Jun 1999 08:00:00 +0200 Sender: chris@iraeugst.net.thinx.ch Message-ID: <37771EC5.6A9CD3D8@thinx.ch> Date: Mon, 28 Jun 1999 09:05:41 +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 alternate 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 ? ThanX -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Christian Rudow E-Mail: Christian.Rudow@thinx.ch ThinX networked business services Stahlrain 10, CH-5200 Brugg ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~