Re: [Q]updating multiple rows with Different values

Thank you very much
this is exactly what I am looking for

As well as the example provided
' case when id=1 then 10 '

- it will work as well.

Now just one more question:
I will not have a lot of values to update (less than a 1000
at a time) -- but the values for col1 will be text that is
up to 64K. So I will not be able to construct SQL strings
and just send them (because it will probably exceed the character
limits for the SQL statements).

Instead, what I plan to do is to generate an sql string as prepared
statement in PDO, and then bind values to it, so I will have

UPDATE tbl_1 SET col1 = t.col1 FROM (VALUES
(':val1', ':id1')
(':val2', ':id2')
(':val3', ':id3')
) AS t(id, col1)

$count=0;
foreach ($upd_arr as $upd_row )
{
bindValue(':val'.$count,$upd_row->val);
bindValue(':id'.$count,$upd_row->id);
$count=$count+1
}

Is this, aproximately, how I should be doing the update?
Is there a limit on the amount of total size of the statement
when gets out of PDO and into postgres

If yes, what is it?
I will just split the loop into chunks,
just wanted to know.

Thank you again for such a quick help.

On Sun, 23 Nov 2008 10:11:56 +0100, "Gerhard Heift"
<ml-postgresql-20081012-3518(at)gheift(dot)de> said:
> On Sat, Nov 22, 2008 at 10:04:48PM -0500, V S P wrote:
> > Hello,
> > searched documentation, FAQ and mailing list archives
> > (mailing list archive search is volumous :-) )
> >
> > but could not find an answer:
> >
> > I would like to be able to update
> > several rows to different values at the same time
> >
> > In oracle this used to be called Array update or
> > 'collect' update or 'bulk' update -- but those
> > keywords did not bring anything for Postgresql.
> >
> > for example tbl_1 has two columns id and col1
> >
> >
> > update tbl_1 set
> > col1=3 where id=25,
> > col1=5 where id=26
>
> Something like this?
>
> UPDATE tbl_1 SET col1 = t.col1 FROM (VALUES
> (25, 3)
> (26, 5)
> ) AS t(id, col1)
> WHERE tbl_1.id = t.id;
>
> > I am using PHP PDO (and hoping that if there is a mechanism
> > within postgresql to do that PDO will support it as well).
> >
> > Thank you in advance,
> > VSP
>
> Regards,
> Gerhard
--
V S P
toreason(at)fastmail(dot)fm

--
http://www.fastmail.fm - Or how I learned to stop worrying and
love email again