[ILUG] Database question...

[Raymond O'Donnell]

On 17/12/2012 11:56, Braun Brelin wrote:
> Hi all,
> 
> I've been searching on google and asking questions in forums with no real
> result, so I figured I'd try here:
> 
> 
> I'm using a postgres 9.1 database on Ubuntu Linux 12.04
> 
> I have a table that looks like this:
> 
> id serial primary key not null
> item_name varchar (40)
> item_amount integer
> 
> I then want to add a new colum via alter table which is also an integer,
> such as item_amount_minimum_value
> 
> Assuming that I already have data in the table for the other three columns,
> how can I populate the new column with data?
> 
> I've tried doing this:  update my_table set item_amount_minimum_value =
> (10,5,2,10,20)
> but this fails as postgres thinks that the values are a row record and not
> a list of integers.
> 
> I supposed I could do something like this:  insert into my_table
> (item_amount_minimum_value) values (10),(5),(2), however is it possible to
> do this with an update rather than an insert?

As others have said, you need to do an update rather than an insert,
since you're updating rows that already exist.

How about loading the data in another table with a common id column, and
then doing an update from that one? - something like this (untested):

update first_table a
set item_amount_minimum_value
from second_table b
where a.id = b.id;

HTH,

Ray.


-- 
Raymond O'Donnell :: Tela Design Ltd
www.teladesign.ie :: Software Development & Consultancy