Update in PL/SQL

Question

Given two tables, the product table and the order table, I am trying to update(update) the product description of the products table when the product status is 'INACTIVE' and the order table status is 'CLOSED'

create table PEDIDOS
(
  CLIENTE_ID NUMBER not null,
  PRODUTO_ID NUMBER not null,
  DT_INSTAL  DATE not null,
  STATUS     VARCHAR2(40),
  PEDIDO_ID  NUMBER not null
)

create table PRODUTOS
(
  PRODUTO_ID NUMBER not null,
  DESCRICAO  VARCHAR2(40) not null,
  VALOR      VARCHAR2(13) not null,
  STATUS     VARCHAR2(13) not null,
  TIPO       VARCHAR2(1)
)

For example, I have a product of id 1 and description 'televisión', if that television is inactive and in the order table (according to id) is closed, I want to change the description to any other name. How do I join two tables in the UPDATE command? What I’m getting, if anyone can help, thank you.

Answer 1

The command update can only be applied to one table at a time. If you want to ensure that the two occur together, you can use a transaction. Or you can fire a Trigger that after the first update, check its condition and perform the second update.