Help with update statement [message #659815] |
Tue, 31 January 2017 02:38 |
|
apenkov
Messages: 20 Registered: October 2012
|
Junior Member |
|
|
Hello,
I need help with an update statement. I have table1 like this:
ID; Code; Name
12345; AA001; 101_TestName1
54321; AA002; 102_TestName2
67890; AA003; 103_TestName3
And another table2 like this:
New_ID; New_Name
123; 123_TestName1
456; 456_TestName2
789; 789_TestName3
I would like to update the first 3 digits of column "Name" of table1 either with the value of New_ID or substring,0,3 of New_Name column from table2 where "Code" like 'AA%' and "Name" like '1%'
So result to be like:
ID; Code; Name
12345; AA001; 123_TestName1
54321; AA002; 456_TestName2
67890; AA003; 789_TestName3
I am trying with this, but TOAD do not accept it.
update table1
set Name = (select New_Name from table2)
where code 'AA%'
and Name like '1%'
It result in error like:
ORA-01427: single-row subquery returns more than one row
Thanks in advance!
|
|
|
Re: Help with update statement [message #659816 is a reply to message #659815] |
Tue, 31 January 2017 02:57 |
|
Michel Cadot
Messages: 68665 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
The error is expected.
Use a correlated subquery; I could show you how to do it but I have not your tables and data.
Reminder from your previous topic:
Michel Cadot wrote on Mon, 15 June 2015 20:58
What about posting a test case?
BlackSwan wrote on Thu, 18 July 2013 16:24Please read and follow the forum guidelines, to enable us to help you:
http://www.orafaq.com/forum/t/88153/0/
Also always post your Oracle version, with 4 decimals, as solution depends on it.
[Updated on: Tue, 31 January 2017 02:59] Report message to a moderator
|
|
|
|
|
Re: Help with update statement [message #659861 is a reply to message #659818] |
Wed, 01 February 2017 07:36 |
Bill B
Messages: 1971 Registered: December 2004
|
Senior Member |
|
|
If the pattern I saw is correct then try
update table1 a
set a.name =
(select b.new_name
from table2 b
where substr(a.name,5) = substr(b.name,5))
where code 'AA%'
and Name like '1%';
|
|
|
|
|