Thread: 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 > > 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... > 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 > > > 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. > "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? > > http://archives.postgresql.org >
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: >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 > >