Update of multiple records

shell100 · Posted: Wed May 10, 2006 7:17 pm

Hi ,

As per my requirement i have to update multile rows in table.
For ex i have two element A & B & table could have multiple rows with this combination as below:

A A A
B B B
C Z T

I have to update third field(C,Z,T) with some constant value(let say Y) for all the records having element A & B.

How can go for this. I think i have to use cursor with update. Please let me know how can i use that. Some hint would work......

Thanks,

DavidatK · Posted: Wed May 10, 2006 10:11 pm

Shell,

I'm sorry, but I don't understand your requirement.

Would you please post what you want the result to look like?

Thanks,

Dave

shrivatsa · Posted: Thu May 11, 2006 9:36 am

Hi Shell,

I am also didn't understand your question, but you can try the following query to update one or more rows.

UPDATE table
SET column1 = ( SELECT col1 FROM .... ) ,
column2 = ( SELECT col2 FROM .... )
WHERE ....

or you can bracket the columns:

UPDATE table
SET ( column1 , column2 ) = ( SELECT col1, col2 FROM .... )
WHERE ...

in this way we can update more than one row at a time.

Is this is your requirement

shell100 · Posted: Thu May 11, 2006 10:05 am

FOR Example:

Name Class Record type
A B C
A B Z
A B T

Results Required :

Name Class Record type
A B Y
A B Y
A B Y

what i need to do is update Record Type as 'Y' where Name = A AND Class = B.

shrivatsa · Posted: Thu May 11, 2006 10:20 am

Hi,

UPDATE TEST1
SET RECTYPE='Y'
WHERE NAME='A' AND CLASS='B';

Run this query. You will get your requirement.

Result of the query when I run

REPORT

NAME CLASS RECTYPE
---- ----- -------
A B Y
A B Y
A B Y

*** END ***

twissi · Posted: Thu May 11, 2006 5:49 pm

Hi Shell,

In addition to all aswers given above, I would like to tell you that, you don't have to use a CURSOR for updating a table. A straight forward query will update all records satisfying your condition in the WHERE clause.

(Mind you, this can also be accomplished using a CURSOR, but it causes multiple execution of the same query that could have been achieved in a single non-cursor query.)

Hope this makes sense!

Cheers, Twissi.