pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedUpdate from join
5+ messages / 3 participants
[nested] [flat]
* Update from join
@ 2006-07-07 10:29 Gary Stainburn <gary.stainburn@ringways.co.uk>
0 siblings, 2 replies; 5+ messages in thread
From: Gary Stainburn @ 2006-07-07 10:29 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Update from join
@ 2006-07-07 11:39 Gary Stainburn <gary.stainburn@ringways.co.uk>
parent: Gary Stainburn <gary.stainburn@ringways.co.uk>
1 sibling, 0 replies; 5+ messages in thread
From: Gary Stainburn @ 2006-07-07 11:39 UTC (permalink / raw)
To: pgsql-sql
On Friday 07 July 2006 11:29, Gary Stainburn wrote:
> 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.
>
I came up with:
goole=# update test1 set ud_stock=(select s_stock_no from test2 where
test1.ud_registration = test2.s_regno) where ud_stock ~ '^[NU][LD]$';
UPDATE 679
but as you can see, it updated a hell of a lot more than 21 rows.
Can anyone improve on this?
Gary
--
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Update from join
@ 2006-07-07 12:06 Michael Glaesemann <grzm@seespotcode.net>
parent: Gary Stainburn <gary.stainburn@ringways.co.uk>
1 sibling, 0 replies; 5+ messages in thread
From: Michael Glaesemann @ 2006-07-07 12:06 UTC (permalink / raw)
To: Gary Stainburn <gary.stainburn@ringways.co.uk>; +Cc: pgsql-sql
On Jul 7, 2006, at 6:29 , Gary Stainburn wrote:
> 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.
I think this should do the trick for you:
UPDATE test1
SET ud_stock = s_stock_no
FROM test2
WHERE ud_registration = s_regno
AND ud_stock IS NULL -- limits update just to cases where
ud_stock IS NULL
This statement also shows the WHERE clause doing double duty: it
contains both a JOIN condition (ud_registration = s_regno) and a
restriction (AND ud_stock IS NULL). In a SELECT statement I like to
keep these separate, something like:
SELECT ud_stock, s_stock_no
FROM test1
JOIN test2 ON (ud_registration = s_regno)
WHERE ud_stock IS NULL
However, the UPDATE syntax doesn't provide for this. (See http://
www.postgresql.org/docs/current/interactive/sql-update.html for more
details.)
In your SELECT example you've got a slightly different join condition
and an additional restriction in the WHERE clause. You may want to
add these to the UPDATE statement if these are necessary. To
illustrate my point about separating restriction from join
conditions, this is how you could rewrite your SELECT:
SELECT ud.ud_id
, ud.ud_registration
, ud.ud_stock
, s.s_stock_no
FROM test1 ud
JOIN test2 s ON (upper(ud.ud_registration) = upper(s.s_regno))
WHERE upper(ud.ud_stock) ~ '^[NU][LD]$';
The AND ud_stock IS NULL condition isn't really necessary, as you
said the registration numbers are always the same--without the IS
NULL the UPDATE will just overwrite the ud_stock number with the same
value. However, depending on your table size and the indexes you have
on the tables, it might perform better with the IS NULL condition.
Hope this helps.
Michael Glaesemann
grzm seespotcode net
^ permalink raw reply [nested|flat] 5+ messages in thread
* update from join
@ 2009-05-14 13:27 Gary Stainburn <gary.stainburn@ringways.co.uk>
0 siblings, 1 reply; 5+ messages in thread
From: Gary Stainburn @ 2009-05-14 13:27 UTC (permalink / raw)
To: pgsql-sql
I know I should be able to do this but my brain's mashed today
I have a stock table with
s_stock_no varchar primary key
s_vin varchar
s_updated timestamp
s_superceded boolean
It is possible for the same vin to exist on stock if we have sold and then
bought back a vehicle, e.g. as a part exchange.
Every time a vehicle is inserted/updated the s_updated field is update.
How can I update the table so that for each s_vin, if a record does not have
the most recent s_updated value, s_superceded is set to true?
I can get the most recent value by running:
select * from (select s_vin,
count(s_updated) as numb,
max(s_updated)::timestamp as latest
from stock
group by s_vin) foo
where numb > 1;
but I can't seem to get how I can convert this to an update statement. The num
> 1 simply removed all vehicles with only one record.
I seem to think I need an update..... from..... statement
--
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: update from join
@ 2009-05-14 15:44 Rob Sargent <robjsargent@gmail.com>
parent: Gary Stainburn <gary.stainburn@ringways.co.uk>
0 siblings, 0 replies; 5+ messages in thread
From: Rob Sargent @ 2009-05-14 15:44 UTC (permalink / raw)
To: pgsql-sql; +Cc: Gary Stainburn <gary.stainburn@ringways.co.uk>
I wonder if this works:
update stock s set s_superceded = true
where s.s_updated < (select max(t.s_updated) from stock t where t.s_vin =
s.s_vin)
On Thu, May 14, 2009 at 7:27 AM, Gary Stainburn <
gary.stainburn@ringways.co.uk> wrote:
> I know I should be able to do this but my brain's mashed today
>
> I have a stock table with
>
> s_stock_no varchar primary key
> s_vin varchar
> s_updated timestamp
> s_superceded boolean
>
> It is possible for the same vin to exist on stock if we have sold and then
> bought back a vehicle, e.g. as a part exchange.
>
> Every time a vehicle is inserted/updated the s_updated field is update.
>
> How can I update the table so that for each s_vin, if a record does not
> have
> the most recent s_updated value, s_superceded is set to true?
>
> I can get the most recent value by running:
>
> select * from (select s_vin,
> count(s_updated) as numb,
> max(s_updated)::timestamp as latest
> from stock
> group by s_vin) foo
> where numb > 1;
>
>
> but I can't seem to get how I can convert this to an update statement. The
> num
> > 1 simply removed all vehicles with only one record.
>
> I seem to think I need an update..... from..... statement
>
> --
> 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
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2009-05-14 15:44 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-07-07 10:29 Update from join Gary Stainburn <gary.stainburn@ringways.co.uk>
2006-07-07 11:39 ` Gary Stainburn <gary.stainburn@ringways.co.uk>
2006-07-07 12:06 ` Michael Glaesemann <grzm@seespotcode.net>
2009-05-14 13:27 update from join Gary Stainburn <gary.stainburn@ringways.co.uk>
2009-05-14 15:44 ` Re: update from join Rob Sargent <robjsargent@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox