UPDATE values from an array?

[The Plankmeister]

Hi there...

Is it possible to write an UPDATE or INSERT query, where the new value comes
from an array? For example:

UPDATE table_a SET column_x = [@array1]
WHERE column_y = [@array2];

It's a query (for argument's sake called query1) in an Access database,
which I'm accessing as a stored procedure through PHP, so I would run some
php code that looked possibly like this:

$array1 = array("a","b"," c","d");
$array2 = array(1,2,3,4);

$str_sql = "exec query1 $array1, $array2";
my_run_query_fu nction($str_sql );


But is it possible? The information I can find about it seems to say that it
has to be an array created inside the query, but if it's a stored procedure,
this isn't possible, is it?

Hope someone can help...

Plankmeister.

[Simon Hayes]

Re: UPDATE values from an array?


"The Plankmeister" <plankmeister_N OSPAM_@hotmail. com> wrote in message
news:4002fc74$0 $138$edfadb0f@d read11.news.tel e.dk...[color=blue]
> Hi there...
>
> Is it possible to write an UPDATE or INSERT query, where the new value[/color]
comes[color=blue]
> from an array? For example:
>
> UPDATE table_a SET column_x = [@array1]
> WHERE column_y = [@array2];
>
> It's a query (for argument's sake called query1) in an Access database,
> which I'm accessing as a stored procedure through PHP, so I would run some
> php code that looked possibly like this:
>
> $array1 = array("a","b"," c","d");
> $array2 = array(1,2,3,4);
>
> $str_sql = "exec query1 $array1, $array2";
> my_run_query_fu nction($str_sql );
>
>
> But is it possible? The information I can find about it seems to say that[/color]
it[color=blue]
> has to be an array created inside the query, but if it's a stored[/color]
procedure,[color=blue]
> this isn't possible, is it?
>
> Hope someone can help...
>
> Plankmeister.
>[/color]

I don't know about Access, but for SQL Server, one possible way would be to
parse the arrays on the client side, then call a stored procedure multiple
times:

exec dbo.MyProc 'a', 1
exec dbo.MyProc 'b', 2
etc.

Alternatively, you could look at transforming the arrays into a two-column
table, then join on it:

update dbo.MyTable
set column_x = a.x
where exists (select *
from dbo.ArrayTable a
where MyTable.column_ y = a.y)

But this may not work in Access, of course. For more information on arrays
in SQL Server, you can look here:

http://www.sommarskog.se/arrays-in-sql.html

Simon

[--CELKO--]

Re: UPDATE values from an array?

>> Is it possible to write an UPDATE or INSERT query, where the new
value comes
from an array? <<

There are no arrays in SQl, only tables. That why using a "tbl-"
prefix not only violates the ISO-11179 rules for naming data elements,
but looks so absurd.

You can stuff the array elements into rows in a table, then do a
regular SQL statement with the table.

If Yukon gets up to SQL-92 standards, you can use a table constructor
in an INSERT INTO statement and other places.

UPDATE Table_a
SET column_x = (SELECT T2.x
FROM T2
WHERE T2.keycol = Table_a.keycol) ;

The rest of your code was not SQL, but some dialect that allows $,
misuses double quotes, etc.

"Caesar: Pardon him, Theodotus. He is a barbarian and thinks the
customs of his tribe and island are the laws of nature." - Caesar and
Cleopatra; George Bernard Shaw 1898