Home » SQL & PL/SQL » SQL & PL/SQL » Help with update statement (Oracle 11g, 2, Solaris 11)
Help with update statement [message #659815] Tue, 31 January 2017 02:38 Go to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 65389
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:24
Please 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 #659817 is a reply to message #659815] Tue, 31 January 2017 03:00 Go to previous messageGo to next message
live4learn
Messages: 41
Registered: September 2013
Location: Bangalore, India
Member
what is the relationship between id and new_id or what is the logical relationship between the rows of tables?

Re: Help with update statement [message #659818 is a reply to message #659815] Tue, 31 January 2017 03:01 Go to previous messageGo to next message
Michel Cadot
Messages: 65389
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Also your specification and result don't match.
At least one is wrong.

Re: Help with update statement [message #659861 is a reply to message #659818] Wed, 01 February 2017 07:36 Go to previous messageGo to next message
Bill B
Messages: 1718
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%';
Re: Help with update statement [message #659862 is a reply to message #659861] Wed, 01 February 2017 07:57 Go to previous messageGo to next message
joy_division
Messages: 4823
Registered: February 2005
Location: East Coast USA
Senior Member
Bill, second where clause is invalid. Maybe OP can figure it out.
Re: Help with update statement [message #659863 is a reply to message #659862] Wed, 01 February 2017 08:00 Go to previous message
Bill B
Messages: 1718
Registered: December 2004
Senior Member
Thats what I get for copy pasting. lol

[Updated on: Wed, 01 February 2017 08:01]

Report message to a moderator

Previous Topic: Procedure out of statements
Next Topic: constraint error
Goto Forum:
  


Current Time: Sat Feb 24 09:13:06 CST 2018

Total time taken to generate the page: 0.03680 seconds