updating many columns

I need to update/insert into a table which has 98
columns, does anyone know of any neat ways I can do
this? currently my query is just plain text with
placeholders like this:

update:
$query = 'UPDATE table SET column1=column1+? ... column98=column98+?
WHERE condition1=? AND condition2 = ?';

insert:

$query = 'INSERT INTO table VALUES (? .. ?)';

does anyone know a better way of doing this (apart from the obvious
way of having a loop from 1 to 98 and concatenting values to $query).

thanks





--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

RE: updating many columns

I use this for some wide tables, using a hash to handle the data in perl,
but its really the same principle as your loop:
$statement = 'insert into vendors (' . join(',',(keys %field_data)) . ')
VALUES(' . '?,' x ((scalar (keys %field_data))-1) . '?)';
$insert_handle = $dbh->prepare($statement);
$rc=$insert_handle->execute( (values %field_data) );

The update would be similar, but use map:
$statement= 'update table set ' . join(',',(map
{"$_=$_+?",keys(%field_data)))) . ' where condition1=? AND condition2 = ?'
(not debugged)

Hope this helps,
John Chambers

> -----Original Message-----
> From: mayuran [mailto:mayuran.yogarajah@casalemedia.com]
> Sent: Thursday, March 18, 2004 2:05 PM
> To: perl@lists.mysql.com
> Subject: updating many columns
>
>
> I need to update/insert into a table which has 98
> columns, does anyone know of any neat ways I can do
> this? currently my query is just plain text with
> placeholders like this:
>
> update:
> $query = 'UPDATE table SET column1=column1+? ... column98=column98+?
> WHERE condition1=? AND condition2 = ?';
>
> insert:
>
> $query = 'INSERT INTO table VALUES (? .. ?)';
>
> does anyone know a better way of doing this (apart from the obvious
> way of having a loop from 1 to 98 and concatenting values to $query).
>
> thanks
>
>
>
>
>
> --
> MySQL Perl Mailing List
> For list archives: http://lists.mysql.com/perl
> To unsubscribe:
> http://lists.mysql.com/perl?unsub=JLChambers@kliklok.com
>

--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

RE: updating many columns

I use this for some wide tables, using a hash to handle the data in perl,
but its really the same principle as your loop:
$statement = 'insert into vendors (' . join(',',(keys %field_data)) . ')
VALUES(' . '?,' x ((scalar (keys %field_data))-1) . '?)';
$insert_handle = $dbh->prepare($statement);
$rc=$insert_handle->execute( (values %field_data) );

The update would be similar, but use map:
$statement= 'update table set ' . join(',',(map
{"$_=$_+?",keys(%field_data)))) . ' where condition1=? AND condition2 = ?'
(not debugged)

Hope this helps,
John Chambers

> -----Original Message-----
> From: mayuran [mailto:mayuran.yogarajah@casalemedia.com]
> Sent: Thursday, March 18, 2004 2:05 PM
> To: perl@lists.mysql.com
> Subject: updating many columns
>
>
> I need to update/insert into a table which has 98
> columns, does anyone know of any neat ways I can do
> this? currently my query is just plain text with
> placeholders like this:
>
> update:
> $query = 'UPDATE table SET column1=column1+? ... column98=column98+?
> WHERE condition1=? AND condition2 = ?';
>
> insert:
>
> $query = 'INSERT INTO table VALUES (? .. ?)';
>
> does anyone know a better way of doing this (apart from the obvious
> way of having a loop from 1 to 98 and concatenting values to $query).
>
> thanks
>
>
>
>
>
> --
> MySQL Perl Mailing List
> For list archives: http://lists.mysql.com/perl
> To unsubscribe:
> http://lists.mysql.com/perl?unsub=JLChambers@kliklok.com
>

--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org