Home » SQL & PL/SQL » SQL & PL/SQL » Update using sequence
Update using sequence [message #293081] Thu, 10 January 2008 16:56 Go to next message
TLegend33
Messages: 203
Registered: March 2005
Senior Member
I have the below table, test5:

SQL> desc test5
 Name                                                              Null?    Type
 ----------------------------------------------------------------- -------- --------------------------------------------
 A_ID                                                                       NUMBER
 EMAILADDRESS                                                               VARCHAR2(50)
 B_ID                                                                       NUMBER


SQL> select * from test5;

      A_ID EMAILADDRESS                                             B_ID
---------- -------------------------------------------------- ----------
     12345 goodemail@email.com                                         1
     12346 goodemail@email.com                                         1
     12347 goodemail@email.com                                         1
     22345 bobsemail@email.com                                         2
     22346 bobsemail@email.com                                         2
     22347 bobsemail@email.com                                         2


What I'd like to do is replace the existing emails with sequentially numbered ambiguous emails that have the same b_id. Below, is an example of my desired output:
      A_ID EMAILADDRESS                                             B_ID
---------- -------------------------------------------------- ----------
     12345 mystery_email_1@online.com                                  1
     12346 mystery_email_1@online.com                                  1
     12347 mystery_email_1@online.com                                  1
     22345 mystery_email_2@online.com                                  2
     22346 mystery_email_2@online.com                                  2
     22347 mystery_email_2@online.com                                  2


Here is my update query that fails because I can't a sequence in an update statement:
update test5 a
set a.emailaddress = (select 'mystery_email_'||test_seq.nextval||'@online.com' from test5 a, test5 b
where a.id = b.id);


I thought of using a trigger, but I won't get the desired syntax on the email addresses I'm entering.

Any thoughts of how I could update these emails without using a sequence in my update statement?
Thanks.
Re: Update using sequence [message #293086 is a reply to message #293081] Thu, 10 January 2008 18:54 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8627
Registered: November 2002
Location: California, USA
Senior Member
From what you provided, it looks like you should be able to just update the emailaddress by concatenating the b_id:

update test5 a
set a.emailaddress =  'mystery_email_' || a.b_id || '@online.com';

[Updated on: Thu, 10 January 2008 18:58]

Report message to a moderator

Re: Update using sequence [message #293273 is a reply to message #293086] Fri, 11 January 2008 09:14 Go to previous messageGo to next message
TLegend33
Messages: 203
Registered: March 2005
Senior Member
You're exactly right. I have a real knack for making things more complicated than they need to be.
Thanks for your help, I appreciate it. Below is the query that worked for me...quite easy when you actually see it.
update test5 a
set a.emailaddress = (select 'mystery_email_'||b.id||'@online.com' from test5 b
where a.id = b.id);
Re: Update using sequence [message #293440 is a reply to message #293273] Sat, 12 January 2008 18:36 Go to previous message
Barbara Boehmer
Messages: 8627
Registered: November 2002
Location: California, USA
Senior Member
TLegend33 wrote on Fri, 11 January 2008 07:14

You're exactly right. I have a real knack for making things more complicated than they need to be.
Thanks for your help, I appreciate it. Below is the query that worked for me...quite easy when you actually see it.
update test5 a
set a.emailaddress = (select 'mystery_email_'||b.id||'@online.com' from test5 b
where a.id = b.id);




That is still unnecessarily overly complicated. Why not just use the simple update statement that I provided, as demonstrated below? I am guessing that perhaps the problem is that your original question does not correspond to the actual problem, since your original question has a_id and b_id columns and your supposed solution has only an id column.




SCOTT@orcl_11g> SELECT * FROM test5
  2  /

      A_ID EMAILADDRESS                                             B_ID
---------- -------------------------------------------------- ----------
     12345 goodemail@email.com                                         1
     12346 goodemail@email.com                                         1
     12347 goodemail@email.com                                         1
     22345 bobsemail@email.com                                         2
     22346 bobsemail@email.com                                         2
     22347 bobsemail@email.com                                         2

6 rows selected.

SCOTT@orcl_11g> update test5 a
  2  set a.emailaddress =  'mystery_email_' || a.b_id || '@online.com'
  3  /

6 rows updated.

SCOTT@orcl_11g> SELECT * FROM test5
  2  /

      A_ID EMAILADDRESS                                             B_ID
---------- -------------------------------------------------- ----------
     12345 mystery_email_1@online.com                                  1
     12346 mystery_email_1@online.com                                  1
     12347 mystery_email_1@online.com                                  1
     22345 mystery_email_2@online.com                                  2
     22346 mystery_email_2@online.com                                  2
     22347 mystery_email_2@online.com                                  2

6 rows selected.

SCOTT@orcl_11g> 


Previous Topic: IF..THEN statements
Next Topic: Curious Behavior on Old Oracle 10g Server
Goto Forum:
  


Current Time: Mon Dec 05 05:03:22 CST 2016

Total time taken to generate the page: 0.04672 seconds