UPDATE only rows which match another table [message #605147] |
Tue, 07 January 2014 11:34 |
|
alexandercioby
Messages: 3 Registered: January 2014 Location: Romania, Bucharest
|
Junior Member |
|
|
Hello,
I have a SQL query where I am trying to update a column USER in a table A from USER coloumn data in another table B(STATUS 6). I am getting the error:Oracle Single-row subquery returns more than one row error
tables look like :
Table A: ID USER
1 2
3 10
Table B: ID ID_FROM_TABLE_A USER STATUS
1 1 18 6
2 1 19 6
3 3 40 10
Can anyone help me?
Thank you in advance
[EDITED by LF: applied [code] tags]
[Updated on: Tue, 07 January 2014 14:25] by Moderator Report message to a moderator
|
|
|
|
Re: UPDATE only rows which match another table [message #605154 is a reply to message #605147] |
Tue, 07 January 2014 11:54 |
joy_division
Messages: 4963 Registered: February 2005 Location: East Coast USA
|
Senior Member |
|
|
alexandercioby wrote on Tue, 07 January 2014 12:34I am getting the error:Oracle Single-row subquery returns more than one row error
Table B: ID ID_FROM_TABLE_A USER STATUS
1 1 18 6
2 1 19 6
3 3 40 10
Yes, because how would you expect Oracle to know which row to use?
Also, please use proper code tags in your post because the columns do not line up and make it difficult to read.
|
|
|
|
|
Re: UPDATE only rows which match another table [message #605297 is a reply to message #605296] |
Wed, 08 January 2014 08:23 |
cookiemonster
Messages: 13917 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
Also the update needs a where clause or it'll set user to null for every record in a that doesn't have a match in b.
And don't use the same table alias for different instances of the same table in the same dml - doing so is just a recipe for confusion and bugs.
|
|
|
|
Re: UPDATE only rows which match another table [message #605350 is a reply to message #605311] |
Wed, 08 January 2014 15:19 |
joy_division
Messages: 4963 Registered: February 2005 Location: East Coast USA
|
Senior Member |
|
|
I'm not going to give the answer outright, but just point out that this part
(select b.USER
from tableB b
join tableA a on b.ID_FROM_TABLE_A=a.ID
where b.id in
is not needed. You will also need to make some adjustments.
You just need a simple correlated sub-query (no two table join in the correlated subquery either) and definitely pay heed to what cookiemonster said.
[Updated on: Wed, 08 January 2014 15:20] Report message to a moderator
|
|
|