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 nullud_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 nulls_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 |
ND90122489 | 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
