s**********i
发帖数: 711 | 1 table1 has bunch of columns, with 'id' as primary key, and
I need to update a column a with max value of table2.b, for
corresponding id.
I tried
replace into table1 (id, a) select id, max(b) from table2 group by id;
this can put the max(b) into table1 a column, but in same time overwrites
all other columns in table1 to default value... which certainly is not
what I want.
anyone know an answer? TIA | m******t
发帖数: 2416 | 2
Right now I can think of a way to do it in two steps -
select each distinct id, and its max b value into a temp table,
then update table1 with this temp table.
I guess it's possible to do it with one single sql, but I
can't experiment any of my thoughts at this point...
【在 s**********i 的大作中提到】
: table1 has bunch of columns, with 'id' as primary key, and
: I need to update a column a with max value of table2.b, for
: corresponding id.
: I tried
: replace into table1 (id, a) select id, max(b) from table2 group by id;
: this can put the max(b) into table1 a column, but in same time overwrites
: all other columns in table1 to default value... which certainly is not
: what I want.
: anyone know an answer? TIA
| s**********i
发帖数: 711 | 3 thanks. since this likely can't be done in one step,
I've decide just modify my scripts to do it in the
program rather than SQL alone. | f***g
发帖数: 10 | 4
If using SQL Server, these 2 statements can be combined as one like this
UPDATE table1
SET a = TEMP2.maxB
FROM table1
INNER JOIN (select id, max(b) as maxB from table2 group by id) TEMP2
ON table1.id = TEMP2.id
Not sure about other DBs such as Oracle... | xt
发帖数: 17532 | 5
If it is on SQL Server, most probably Sybase ASE supports it too.
【在 f***g 的大作中提到】
:
: If using SQL Server, these 2 statements can be combined as one like this
: UPDATE table1
: SET a = TEMP2.maxB
: FROM table1
: INNER JOIN (select id, max(b) as maxB from table2 group by id) TEMP2
: ON table1.id = TEMP2.id
: Not sure about other DBs such as Oracle...
| s**********i
发帖数: 711 | 6
thanks... but it's not SQL server... I'm not going to say it
as aya would teach me again to use postgresql :)
【在 f***g 的大作中提到】
:
: If using SQL Server, these 2 statements can be combined as one like this
: UPDATE table1
: SET a = TEMP2.maxB
: FROM table1
: INNER JOIN (select id, max(b) as maxB from table2 group by id) TEMP2
: ON table1.id = TEMP2.id
: Not sure about other DBs such as Oracle...
| t****s
发帖数: 141 | 7 MySql? haha
【在 s**********i 的大作中提到】
:
: thanks... but it's not SQL server... I'm not going to say it
: as aya would teach me again to use postgresql :)
|
|
