From: | "Dan Winslow" <d(dot)winslow(at)cox(dot)net> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Updating table with max from another table |
Date: | 2002-11-19 18:22:24 |
Message-ID: | AjvC9.69703$hb.17906@news1.central.cox.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Dan Winslow | 2002-11-19 18:27:52 | Update table with max occurance from another table |
Previous Message | 2002-11-19 18:20:28 | Re: DECLARE CURSOR |