Update 3 rows at a time

[equalive]

Hi friends,

Can anyone guide me on this.

Just assume i have table name "Alphabet" and a field name "Name".

Inside the table contains:

Name
A(1st row)
B(2nd row)
C(3rd row)

and i wana update
A -> D
B -> E
C -> F

How is it possible to do using one single SQL?

I really appreciate your reply.

Thanks guys.

[PBsoft]

Re: Update 3 rows at a time

> Just assume i have table name "Alphabet" and a field name "Name".[color=blue]
>
> Name
> A(1st row)
> B(2nd row)
> C(3rd row)
> and i wana update
> A -> D
> B -> E
> C -> F
> How is it possible to do using one single SQL?[/color]

UPDATE AlphaBet SET AlphaBet.Name = Chr(Asc([Name])+3)
WHERE (((AlphaBet.Nam e)="A")) OR (((AlphaBet.Nam e)="B")) OR (((AlphaBet.Nam e)="C"));

--
PBsoft di Gabriele Bertolucci

https://www.pbsoft.it

skype: pbsoftsolution

[equalive]

Re: Update 3 rows at a time

Yes, its working fine with your coding, Thanks.

But how if it is not an alphabet or a word such as

Mouse -> Snake
Snake -> Eagle
Deer -> Tiger

Earlier i juzt use Alphabatical so that easy for the reader to
understand. Can you guide me on above situation if it is a word rather
than alphabet. Thanks again.

[Terry Kreft]

Re: Update 3 rows at a time

SQL is all about sets.

In this situation where there is nothing to group the original values
together and nothing to describe the update you would have to use separate
updates for each line.

In your original example a rule could be created for the update, this is not
the case with this example.

The only way to get round this would be to create a "translatio n" table, in
this table you would define the original value and the target value you
could then use this to update your table. This really is only any use where
the values appear multiple times and would certainly not be suitable in a
case as trivial as the one you cite.


--

Terry Kreft


"equalive" <equalive@yahoo .com> wrote in message
news:1148029468 .107239.12210@j 73g2000cwa.goog legroups.com...[color=blue]
> Yes, its working fine with your coding, Thanks.
>
> But how if it is not an alphabet or a word such as
>
> Mouse -> Snake
> Snake -> Eagle
> Deer -> Tiger
>
> Earlier i juzt use Alphabatical so that easy for the reader to
> understand. Can you guide me on above situation if it is a word rather
> than alphabet. Thanks again.
>[/color]