Redian新闻
>
SQL question: update a field
avatar
SQL question: update a field# Database - 数据库
s*i
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
avatar
m*t
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

avatar
s*i
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.
avatar
f*g
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...
avatar
xt
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...

avatar
s*i
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...

avatar
t*s
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 :)

相关阅读
logo
联系我们隐私协议©2024 redian.news
Redian新闻
Redian.news刊载任何文章,不代表同意其说法或描述,仅为提供更多信息,也不构成任何建议。文章信息的合法性及真实性由其作者负责,与Redian.news及其运营公司无关。欢迎投稿,如发现稿件侵权,或作者不愿在本网发表文章,请版权拥有者通知本网处理。