Postgres Pro
Downloads
Home   >   mailing lists

Thread: Table UPDATE statement

Table UPDATE statement

From
Venkatesh Babu
Date:
Hello,

I need to update few rows of a table (call it 't') and
need to set just one column col1 (out of around 100
columns... to be exact, our table has 116 columns).
The info about rows to be updated is present in
another table t2. t2 just contains 2 columns (row_key,
new value for col1).

What is the best way to perform update? Can't I give
an update statement like: UPDATE t set
col1=t2.new_col1_val where t1.row_key = t2.row_key
???? I think this kind of support is provided in db2,
but couldn't find how this can be done in postgres.

Thanks,
Venkatesh



__________________________________
Do you Yahoo!?
Read only the mail you want - Yahoo! Mail SpamGuard.
http://promotions.yahoo.com/new_mail

Re: Table UPDATE statement

From
Alban Hertroys
Date:
Venkatesh Babu wrote:
> Hello,
>
> I need to update few rows of a table (call it 't') and
> need to set just one column col1 (out of around 100
> columns... to be exact, our table has 116 columns).
> The info about rows to be updated is present in
> another table t2. t2 just contains 2 columns (row_key,
> new value for col1).
>
> What is the best way to perform update? Can't I give
> an update statement like: UPDATE t set
> col1=t2.new_col1_val where t1.row_key = t2.row_key
> ???? I think this kind of support is provided in db2,
> but couldn't find how this can be done in postgres.

You're close:

UPDATE t1
    SET col1 = t2.new_col1
   FROM t2
  WHERE t1.row_key = t2.row_key;

--
Alban Hertroys
MAG Productions

T: +31(0)53 4346874
F: +31(0)53 4346876
E: alban@magproductions.nl
W: http://www.magproductions.nl

Re: Table UPDATE statement

From
Venkatesh Babu
Date:
Thanks very much :-) this saves lot of time for my
update statements ....

-Venkatesh

--- Alban Hertroys <alban@magproductions.nl> wrote:

> Venkatesh Babu wrote:
> > Hello,
> >
> > I need to update few rows of a table (call it 't')
> and
> > need to set just one column col1 (out of around
> 100
> > columns... to be exact, our table has 116
> columns).
> > The info about rows to be updated is present in
> > another table t2. t2 just contains 2 columns
> (row_key,
> > new value for col1).
> >
> > What is the best way to perform update? Can't I
> give
> > an update statement like: UPDATE t set
> > col1=t2.new_col1_val where t1.row_key = t2.row_key
> > ???? I think this kind of support is provided in
> db2,
> > but couldn't find how this can be done in
> postgres.
>
> You're close:
>
> UPDATE t1
>     SET col1 = t2.new_col1
>    FROM t2
>   WHERE t1.row_key = t2.row_key;
>
> --
> Alban Hertroys
> MAG Productions
>
> T: +31(0)53 4346874
> F: +31(0)53 4346876
> E: alban@magproductions.nl
> W: http://www.magproductions.nl
>


__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around
http://mail.yahoo.com