Update table with max occurance from another table
Well, I *thought* I knew my way around SQL a little bit, but I have been
beating my head on the following problem for a couple days now and I don't
have any idea where to turn next. If you could suggest any strategies or
places to look I would appreciate it very much. Thanks in advance.
Given two table defs :
create table a (
id integer,
maxtype varchar(8)
);
create table b (
id integer,
type varchar(8),
val integer
);
and data rows as follows:
select * from a;
id | maxtype
----+---------
1 |
2 |
(2 rows)
select * from b;
id | type | val
----+-------+-----
1 | type1 | 5
1 | type2 | 6
2 | type1 | 19
2 | type2 | 4
(4 rows)
And given the following task :
update a from b such that a.maxtype is set equal to the b.type whose val
number is the highest for that matching id, that is, the result :
select * from a;
id | maxtype
----+---------
1 | type2
2 | type1
(2 rows)
is to be obtained, how can this be accomplished with SQL statements? I am
looking for a single (perhaps compound ) statement to do it, no procedural
stuff
On Tue, 19 Nov 2002, Dan Winslow wrote:
And given the following task :
update a from b such that a.maxtype is set equal to the b.type whose val
number is the highest for that matching id, that is, the result :
As a starting point, not using the postgresql extensions, or any thought
to make it more efficient, maybe something like:
update a set maxtype=(select type from b where b.id=a.id and
b.val=(select max(val) from b as c where c.id=b.id));
I think using postgres extensions, you could do this as:
update a set maxtype=b.type from (select distinct on (id) id, type
from b order by id, val desc) as b where a.id=b.id;
On Tue, Nov 19, 2002 at 06:27:52PM +0000, Dan Winslow wrote:
And given the following task :
update a from b such that a.maxtype is set equal to the b.type whose val
number is the highest for that matching id, that is, the result :select * from a;
id | maxtype
----+---------
1 | type2
2 | type1
(2 rows)is to be obtained, how can this be accomplished with SQL statements? I am
looking for a single (perhaps compound ) statement to do it, no procedural
stuff
How about:
update a set maxtype =
(select b.type from b where b.id = a.id order by b.val desc limit 1)
-mike
Yes, I tried this, but it doesn't like the order or the limit clause in
sub-selects.
"Mike Beachy" <beachy@marketboy.com> wrote in message
news:20021119191946.GA6703@marketdude.com...
On Tue, Nov 19, 2002 at 06:27:52PM +0000, Dan Winslow wrote:
And given the following task :
update a from b such that a.maxtype is set equal to the b.type whose val
number is the highest for that matching id, that is, the result :select * from a;
id | maxtype
----+---------
1 | type2
2 | type1
(2 rows)is to be obtained, how can this be accomplished with SQL statements? I
am
looking for a single (perhaps compound ) statement to do it, no
procedural
Show quoted text
stuff
How about:
update a set maxtype =
(select b.type from b where b.id = a.id order by b.val desc limit 1)-mike
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
Ok, thank you, this seems to work. I do not undertand what the 'from b as c'
syntax though, much less why its necessary. At any rate, my thanks to you.
"Stephan Szabo" <sszabo@megazone23.bigpanda.com> wrote in message
news:20021119110924.D68336-100000@megazone23.bigpanda.com...
Show quoted text
On Tue, 19 Nov 2002, Dan Winslow wrote:
And given the following task :
update a from b such that a.maxtype is set equal to the b.type whose val
number is the highest for that matching id, that is, the result :As a starting point, not using the postgresql extensions, or any thought
to make it more efficient, maybe something like:update a set maxtype=(select type from b where b.id=a.id and
b.val=(select max(val) from b as c where c.id=b.id));I think using postgres extensions, you could do this as:
update a set maxtype=b.type from (select distinct on (id) id, type
from b order by id, val desc) as b where a.id=b.id;---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
"Dan Winslow" <d.winslow@cox.net> writes:
Yes, I tried this, but it doesn't like the order or the limit clause in
sub-selects.
Then you need a newer version of Postgres; that's been supported since
7.1.
regards, tom lane
Argh, good suggestion. That would make things significantly easier. Thanks.
"Dan Winslow" <d.winslow@cox.net> wrote in message
news:GjwC9.72961$hb.65088@news1.central.cox.net...
Yes, I tried this, but it doesn't like the order or the limit clause in
sub-selects."Mike Beachy" <beachy@marketboy.com> wrote in message
news:20021119191946.GA6703@marketdude.com...On Tue, Nov 19, 2002 at 06:27:52PM +0000, Dan Winslow wrote:
And given the following task :
update a from b such that a.maxtype is set equal to the b.type whose
val
Show quoted text
number is the highest for that matching id, that is, the result :
select * from a;
id | maxtype
----+---------
1 | type2
2 | type1
(2 rows)is to be obtained, how can this be accomplished with SQL statements? I
am
looking for a single (perhaps compound ) statement to do it, no
procedural
stuff
How about:
update a set maxtype =
(select b.type from b where b.id = a.id order by b.val desc limit 1)-mike
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
On Tue, 19 Nov 2002, Dan Winslow wrote:
Ok, thank you, this seems to work. I do not undertand what the 'from b as c'
syntax though, much less why its necessary. At any rate, my thanks to you.
Just to answer this, it's saying from the table b aliased under the name c
because I want the name b to refer to the outer b inside the sub-subquery
so I can say c.id=b.id. I could probably have just done b.id=a.id there
as well, but it felt easier that way.
Show quoted text
"Stephan Szabo" <sszabo@megazone23.bigpanda.com> wrote in message
news:20021119110924.D68336-100000@megazone23.bigpanda.com...On Tue, 19 Nov 2002, Dan Winslow wrote:
And given the following task :
update a from b such that a.maxtype is set equal to the b.type whose val
number is the highest for that matching id, that is, the result :As a starting point, not using the postgresql extensions, or any thought
to make it more efficient, maybe something like:update a set maxtype=(select type from b where b.id=a.id and
b.val=(select max(val) from b as c where c.id=b.id));I think using postgres extensions, you could do this as:
update a set maxtype=b.type from (select distinct on (id) id, type
from b order by id, val desc) as b where a.id=b.id;---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
add 'as foo' to the end of the nested select...
update a set maxtype = (select bla bla bla desc limit 1 as foo)
Dan Winslow wrote:
Show quoted text
Yes, I tried this, but it doesn't like the order or the limit clause in
sub-selects."Mike Beachy" <beachy@marketboy.com> wrote in message
news:20021119191946.GA6703@marketdude.com...On Tue, Nov 19, 2002 at 06:27:52PM +0000, Dan Winslow wrote:
And given the following task :
update a from b such that a.maxtype is set equal to the b.type whose val
number is the highest for that matching id, that is, the result :select * from a;
id | maxtype
----+---------
1 | type2
2 | type1
(2 rows)is to be obtained, how can this be accomplished with SQL statements? I
am
looking for a single (perhaps compound ) statement to do it, no
procedural
stuff
How about:
update a set maxtype =
(select b.type from b where b.id = a.id order by b.val desc limit 1)-mike
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org