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
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
