Update using sequence [message #293081] |
Thu, 10 January 2008 16:56  |
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 #293273 is a reply to message #293086] |
Fri, 11 January 2008 09:14   |
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  |
 |
Barbara Boehmer
Messages: 9104 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>
|
|
|