X-Original-To: pgsql-sql-postgresql.org@postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 03E219FA6B0 for ; Fri, 7 Jul 2006 07:52:59 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 88441-01 for ; Fri, 7 Jul 2006 07:52:52 -0300 (ADT) X-Greylist: delayed 00:21:59.007464 by SQLgrey- Received: from svr4.postgresql.org (svr4.postgresql.org [66.98.251.159]) by postgresql.org (Postfix) with ESMTP id 9EF009FB1B7 for ; Fri, 7 Jul 2006 07:52:50 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.3 Received: from mail.ringways.co.uk (host-80-193-83-141.static.telewest.net [80.193.83.141]) by svr4.postgresql.org (Postfix) with ESMTP id CE0975AF05D for ; Fri, 7 Jul 2006 10:30:51 +0000 (GMT) Received: from eddie.ringways.co.uk ([10.1.1.115]) by mail.ringways.co.uk with esmtp (Exim 4.62) (envelope-from ) id 1Fynab-00014u-ED for pgsql-sql@postgresql.org; Fri, 07 Jul 2006 11:29:37 +0100 From: Gary Stainburn Organization: Ringways Garages Ltd To: pgsql-sql@postgresql.org Subject: Update from join Date: Fri, 7 Jul 2006 11:29:35 +0100 User-Agent: KMail/1.9.1 MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Content-Disposition: inline Message-Id: <200607071129.35291.gary.stainburn@ringways.co.uk> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200607/87 X-Sequence-Number: 25661 I know this is probably a FAQ but Google etc hasn't helped. I have two tables, both with stock number and registration number in. The second table always has the correct stock number, the first doesn't. I want to copy the data across where the stock number is missing. The select with join shows the rows requiring update, but I can't think how to do the update. goole=# \d test1 Table "public.test1" Column | Type | Modifiers -----------------+-----------------------+----------- ud_id | integer | not null ud_registration | character varying(20) | ud_stock | character varying(20) | Indexes: "test1_pkey" PRIMARY KEY, btree (ud_id) goole=# \d test2 Table "public.test2" Column | Type | Modifiers ------------+-----------------------+----------- s_stock_no | character varying(8) | not null s_regno | character varying(12) | Indexes: "test2_pkey" PRIMARY KEY, btree (s_stock_no) goole=# select ud.ud_id, ud.ud_registration, ud.ud_stock, s.s_stock_no from test1 ud, test2 s where upper(ud.ud_registration) = upper(s.s_regno) and upper(ud.ud_stock) ~ '^[NU][LD]$'; ud_id | ud_registration | ud_stock | s_stock_no -------+-----------------+----------+------------ 2359 | YF06YMT | NL | NL6321 2397 | YF06YNC | NL | NL6334 2400 | YB06MJX | ND | ND8402 2422 | YH06VGJ | ND | ND9055 2380 | YF06ZKC | ND | ND9566 2447 | YB06MHX | ND | ND9661 2132 | YC06RZM | ND | ND9527 2429 | YB06SFE | ND | ND9611 2448 | YB06PXV | ND | ND9689 2417 | YF06MXN | ND | ND9012 2489 | YB06HHM | ND | ND9542 2456 | YB06SFJ | ND | ND9675 1666 | YC06RYR | ND | NH310 2455 | YB06ZFH | ND | ND9754 2508 | YF06GWU | NL | NL6245 2655 | YC06SDV | ND | ND9270 2591 | YF06OJM | NL | NL6351 2627 | YC06SGX | ND | ND9057 1795 | YC06SGX | ND | ND9057 2634 | YB06KHT | NL | NL6450 2620 | YF06ZKD | ND | ND9236 (21 rows) -- Gary Stainburn This email does not contain private or confidential material as it may be snooped on by interested government parties for unknown and undisclosed purposes - Regulation of Investigatory Powers Act, 2000