Update from join

From: Gary Stainburn <gary(dot)stainburn(at)ringways(dot)co(dot)uk>
To: pgsql-sql(at)postgresql(dot)org
Subject: Update from join
Date: 2006-07-07 10:29:35
Message-ID: 200607071129.35291.gary.stainburn@ringways.co.uk
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Andrew Sullivan 2006-07-07 11:18:56 Re: Alternative to serial primary key
Previous Message Andreas Joseph Krogh 2006-07-07 09:47:13 Re: Alternative to Select in table check constraint