I have the following tables:
USER
usuario: id number, name varchar2
1 PEPE
2 LUCAS
REPORT
reporte: id number, usuario_id, owner varchar2
1 1 NULL
2 2 NULL
In the report table, I have records that refer to the user table, Is it possible to do an update so that in the ownwer field I can put the name of the agent?
You could use a subquery, something like this:
UPDATE report
SET owner = (SELECT name FROM user WHERE user.id = report.usuario_id)
Good luck!
A different way of doing the update , which is only supported by SQL Server , but which is very good for complex updates, is the update/from statement. It has the advantage that it can have multiple joins, with the condition that each row of the table to be updated is only 1 time in the result of join s.
In your case, which is not so complex, the sentence would look something like:
update report
set owner = user.name
from report
inner join user on user.id = report.usuario_id
You can use aliases, in both tables, with what might look like:
update r
set owner = u.name
from report r
inner join user u on u.id = r.usuario_id
As I said before, the main advantage of this type of update is that you can have multiple join s.