I am trying to update the txstatus field of the trx table with the value of the pgroup field in the producer table in our oracle db. both tables have a field called producer and that is where I am linking them. so for example if the trx table has the following
producer Txstatus
S1234
S1235
S1236
and the producer table has
pgroup producer
GRAD S1234
GRAD S1235
PERIO S1236

I want the trx table to now look like this
txstatus Producer
GRAD S1234
GRAD S1235
PERIO S1236

I am running this:
update trx set "TxStatus"= ( select a."PGroup" from Producer A, TRX b where a."Producer" = b."Producer") and getting the error:
single row subquery returns more then one row
I understand why I get the error, I am just not sure how to correct my code. Could anyone help?