Home » SQL & PL/SQL » SQL & PL/SQL » UPDATE only rows which match another table
UPDATE only rows which match another table [message #605147] Tue, 07 January 2014 11:34 Go to next message
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 #605148 is a reply to message #605147] Tue, 07 January 2014 11:37 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/

UPDATE TABLEA
SET USER = (SELECT ....

the SELECT statement must only return 1 row since USER column can only hold a single value.
Re: UPDATE only rows which match another table [message #605154 is a reply to message #605147] Tue, 07 January 2014 11:54 Go to previous messageGo to next message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
alexandercioby wrote on Tue, 07 January 2014 12:34
I 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 #605290 is a reply to message #605147] Wed, 08 January 2014 07:26 Go to previous messageGo to next message
alexandercioby
Messages: 3
Registered: January 2014
Location: Romania, Bucharest
Junior Member
Sorry for the misunderstanding.
I can't provide you real data because i work in an confident institution.

I rephrase. I want to update USER column values in a tableA from USER column from table B only for users that have STATUS 6.
In tableB I can have for the same ID_FROM_TABLE_A more than one record with the same status.

tableA:

ID   USER
1     20
2     30
3     50  



tableB:

ID  ID_FROM_TABLE_A   STATUS 
10     1                6
24     1                6
37     1                4
45     2                6
76     2                6
98     2                2 



I tried to do something like :

update tableA set 
a.USER=
(select b.USER 
from tableB b
join tableA a on b.ID_FROM_TABLE_A=a.ID 
where b.id in (select min(b.ID)
from tableB b
join tableA a on
 b.ID_FROM_TABLE_A=a.ID and b.STATUS=6 group by b.ID_FROM_TABLE_A))





Re: UPDATE only rows which match another table [message #605296 is a reply to message #605290] Wed, 08 January 2014 08:16 Go to previous messageGo to next message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
Table B does not have a column called USER in your latest example.
even if it did, it looks like your code is too complicated. Simply get the minimum or maximum userid from the table based on a single join.
Re: UPDATE only rows which match another table [message #605297 is a reply to message #605296] Wed, 08 January 2014 08:23 Go to previous messageGo to next message
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 #605311 is a reply to message #605297] Wed, 08 January 2014 11:44 Go to previous messageGo to next message
alexandercioby
Messages: 3
Registered: January 2014
Location: Romania, Bucharest
Junior Member
sorry

the table B looks:

tableB:

ID  ID_FROM_TABLE_A  USER    STATUS 
10     1               20       6
24     1               30       6
37     1               25       4
45     2               15       6
76     2               27       6
98     2               56       2 
Re: UPDATE only rows which match another table [message #605350 is a reply to message #605311] Wed, 08 January 2014 15:19 Go to previous message
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

Previous Topic: weighted average or moving average calculation for stock
Next Topic: SELECT * FROM OPENQUERY([127.0.0.1],'@cmd') (merged)
Goto Forum:
  


Current Time: Fri Mar 29 10:29:46 CDT 2024