SQL error [21000]: ERROR: a subquery used as an expression returned more than one record

The Remesa table has a relationship N to 1 with EnvioRemesa, the issue is that I want to update my delete bit to 1 of the Remesa table, but only update the Remesa that have a EnvioRemesa id = 2. And it is failing me in the query since in the subquery it returns 2 matching records and tries to compare them with r.id. Any way to fix this?

update remesa r 
set borrado=1 
where r.envio_remesa_id= (
                          select r.id from remesa r 
                          inner join envio_remesa er 
                            on r.envio_remesa_id=er.id 
                          where er.id=2
                         )

Answer

You really don’t need a subquery. I bet you can simply inner join directly to the envio_remesa table.

update r  
set r.borrado=1 
FROM remesa as r
inner join dbo.envio_remesa er on r.envio_remesa_id=er.id 
where er.id=2