Thread: How to update more than one row with a different value

Question asked by visitor kamana

How to update more than one row with a different value with in a single column by a single update query.

One possible way could be with the use of CASE statement where the column that needs to be updated depends on some other column value.

A small example:
-- Create the table
CREATE TABLE t_bh ( a NUMBER, b VARCHAR2(100))

-- Insert 3 values.
-- The column b in all the 3 records has 'ABC'.
INSERT INTO t_bh VALUES( 1, 'ABC')
INSERT INTO t_bh VALUES( 2, 'ABC')
INSERT INTO t_bh VALUES( 3, 'ABC')

UPDATE t_bh
SET b =
CASE WHEN a = 1 THEN 'ABC'
WHEN a = 2 THEN 'DEF'
WHEN a = 3 THEN 'GHI'
END ;

Now U have 3 different values updated for b column with a single query.
Not sure if this helps for your requirement.
Thanks.

using like we can update more than one row,with different value in column

Certainly using "case" we can update a row with different values !!!

not sure but try below one
considering emp s reference table,if deptno of type varchar

update emp
set deptno=decode(deptno,10,'ten',20,'twenty','thirty');

try below one
considering emp s reference table,if deptno of type varchar

update emp
set deptno=decode(deptno,10,20,20,30,30);

Hi, im new member to this group,
im updating few sqls at a time
----------------------------------------------------
update ABC_SQL
set sql_text = ( select replace(sql_text,'abc','ABC') FROM
ABC_SQL WHERE SQL_TEXT LIKE '%abc%')
WHERE SQL_TEXT LIKE '%abc%';
commit;
------------------------------------------------
I got -811 sqlcode

my basic need is to change the "abc" to "ABC" in sql_text column from ABC_SQL table.
thanks in adance.

Originally Posted by Geek_Guest

Question asked by visitor kamana

How to update more than one row with a different value with in a single column by a single update query.

There are two ways :

1. CASE
2. Subquery

To update mre than one row with different value........First we have to take Dataadapter and then retrieve all the values from database using command object

Then create commandbuilder object and with the help of adapter object you can update your all different values in different rows
ad.update(cb);

Hope you will find this helpful for you .

Thank you ,

with case its not upadted only 3 row ,updated all the row ,n except then 3 which we update as our wish all other have null if we did't give anythg.So the prob is dis we have to mention updated values for all rows,not only 3(in condition table have more then 3 rows)

Hai Friends, I'm a new member to this.. Almost like a baby among ur knowledge...

I just want to know how to pass parameters to a SQLAdapter updatecommand from a GridView..

And main thing is, I want to update more than one Row. No count should be used..

This is the scenario for me.. Please Help me, Thanks in Advance

Originally Posted by Geek_Guest

Question asked by visitor kamana

How to update more than one row with a different value with in a single column by a single update query.

its is specific to sqlserver:

update t1
set t1.c1=t2.c1,
t2.c2=t2.c2
from table1 t1
inner join table2 t2
on t1.col=t2.col

~Technical Analyst~

Originally Posted by Geek_Guest

Question asked by visitor kamana

How to update more than one row with a different value with in a single column by a single update query.

update table emp
set salary=2000
where empid=39
and
set name='daya'
where city='mumbai';