OraFAQ Forum: SQL & PL/SQL » update statement.

Home » SQL & PL/SQL » SQL & PL/SQL » update statement.

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

update statement. [message #9315]

Tue, 04 November 2003 01:56

Kumar
Messages: 115
Registered: December 1998

Senior Member

Hi.
I have a item column..which contains the values like
AQB10
AQB10
AQA10
ACA10
ACB10
Only the values ending with B10 should be updated to R10.ie.,AQR10,AQR10,ACR10.others should remain the same.
Kindly provide the update statement.

Report message to a moderator

Re: update statement. [message #9316 is a reply to message #9315]

Tue, 04 November 2003 02:09

Maaher
Messages: 7065
Registered: December 2001

Senior Member

UPDATE your_table
   SET column = value
 WHERE substr(item_column,length(item_column)-2, 3) = 'B10';

MHE

Report message to a moderator

Re: update statement. [message #9317 is a reply to message #9316]

Tue, 04 November 2003 02:12

Maaher
Messages: 7065
Registered: December 2001

Senior Member

Even more easy:

UPDATE your_table
   SET column = value
 WHERE item_column LIKE '%B10';

MHE

Report message to a moderator

Re: update statement. [message #9318 is a reply to message #9316]

Tue, 04 November 2003 02:18

Kumar
Messages: 115
Registered: December 1998

Senior Member

But column is not going to be updated with single static value.

AQB10 - AQR10 (Consider AQ also)
AQB10 - AQR10 (Consider AQ also)

AQA10 - ACA10
ACA10 - ACA10

ACB10 - ACR10 (Consider AC also)
ARB10 - ARR10 (Consider AR also)

Only the values ending with B10 should be updated to R10.ie.,AQR10,AQR10,ACR10.others should remain the same.

Report message to a moderator

Re: update statement. [message #9319 is a reply to message #9317]

Tue, 04 November 2003 02:30

Kumar
Messages: 115
Registered: December 1998

Senior Member

Your statement does the following

AQB10 - R10 (AQ ?) It has to be updated as AQR10.
AQB10 - R10 (AQ ?)

AQA10 - ACA10
ACA10 - ACA10

ACB10 - R10 (AC ?)
ARB10 - R10 (AR ?)

Only last 3 values must be changed...

Report message to a moderator

Re: update statement. [message #9320 is a reply to message #9318]

Tue, 04 November 2003 03:38

Maaher
Messages: 7065
Registered: December 2001

Senior Member

Ok, so it should be like this then:

SQL> select *
  2    From t;

        ID ITEM_
---------- -----
         1 AQB10
         2 AQA10
         3 AQB10
         4 AQB10
         5 AQC10
         6 AQA10
         7 ACA10
         8 ACB10
         9 ARB10

9 rows selected.

SQL> Update t
  2     Set item_column = substr(item_column,1,length(item_column)-3)||'R10'
  3   Where item_column like '%B10';

5 rows updated.

SQL> select *
  2    From t;

        ID ITEM_
---------- -----
         1 AQR10
         2 AQA10
         3 AQR10
         4 AQR10
         5 AQC10
         6 AQA10
         7 ACA10
         8 ACR10
         9 ARR10

9 rows selected.

SQL>

MHE

Report message to a moderator

Previous Topic:	Error while committing
Next Topic:	Query from differen tables

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Apr 23 10:34:59 CDT 2024