SQL question: update a field - 未名空间MITBBS历史存档

国际科技财经博客移民网络热点娱乐民生时事公众号

Redian新闻

>未名空间

>Database - 数据库

SQL question: update a field

SQL question: update a field# Database - 数据库

s*i2003-02-27 08:02

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*t2003-02-27 08:02

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*i2003-02-27 08:02

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*g2003-02-27 08:02

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...

xt2003-02-27 08:02

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*i2003-02-27 08:02

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 的大作中提到】