multiple updates in 1 request

[kobayashy]

Hi, I would like to do the following:

update RPC_path set defaultStart = (1,30,20) where pathID in (9,13,20)

I want to update the field defaultStart to 1 for pathID 9, defaultStart to 30 for pathID 13 and
defaultStart to 20 for pathID 20

This line doens't work. Could anyone help me write this line, or at least tell me if it can be done.

Thanks

[jkoopmann]

nomally this is done with one sql statement for each update statement.

BUT, if conditions are perfect you could do something like this:

*** ORACLE SOLUTION ***

SQL> create table rpc_path (defaultstart number, pathid number);

Table created.

SQL> insert into rpc_path values (123,9);

1 row created.

SQL> insert into rpc_path values (123,13);

1 row created.

SQL> insert into rpc_path values (123,20);

1 row created.

SQL> commit;

Commit complete.

SQL> update rpc_path a set a.defaultstart = (select
decode(b.pathid,9,1,13,30,20,20,b.pathid)
2 from rpc_path b where a.pathid = b.pathid);

3 rows updated.

SQL> commit;

Commit complete.

SQL> select * from rpc_path;

DEFAULTSTART PATHID
------------ ----------
1 9
30 13
20 20

[kobayashy]

Currently I have a loop that do one SQL update for each element.

it's just that if I have 200 elements, I would prefer doing it in one sql command to save some time. These update will be done very frequantly in my app, and I would prefer the one sql command method.

Now, about your suggestion, it doesn't seem to work here. I am using the microsoft sql 2000.

I get this error:
Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near 'a'.
Server: Msg 195, Level 15, State 1, Line 3
'decode' is not a recognized function name.

Could this be why.

P.S. I am really new to SQL, been working with ot for couples days only.

[jkoopmann]

it was an oracle solution.

[kobayashy]

right... I missed that line.

Thanks

[Rawhide]

update RPC_path
set defaultStart = Case pathID When 9 Then 1
When 13 Then 30
When 20 Then 20
End
where pathID in (9,13,20)

[kobayashy]

PERFECT!

Thanks a lot Rawhide this is exactly what I need.

And man sql is a strange language!

[Rawhide]

Strange yet beautiful (or at least it pays the bills).