Home / Questions / 318772
Accepted
Jesus_jbs
Asked: 2020-12-29 12:34:57 +0800 CST 2020-12-29 12:34:57 +0800 CST

Mapping attributes of one table to attributes of another table in PL/SQL

  • 772

I have the parts table and the LíneasFacturasProvedores table, both are joined by the OID_P attribute. My question is if there is any way for the PurchasePrice attribute of the Pieces table to be equal to the UnitPrice attribute of the SupplierInvoiceLines table if they are the same piece, that is, if they have the same OID_P.

CREATE TABLE Piezas(
    OID_P               INTEGER,
    nombre              VARCHAR2(50) UNIQUE NOT NULL,
    cantidad            INTEGER NOT NULL,
    precioCompra        NUMBER(5,2) NOT NULL,
    precioVenta         NUMBER(5,2) AS (precioCompra * 1.2),
    numAlmacén          INTEGER NOT NULL,
    CONSTRAINT cantidadPieza CHECK(cantidad >= 0),
    PRIMARY KEY         (OID_P),
    FOREIGN KEY         (numAlmacén) REFERENCES Almacenes
);

CREATE TABLE LíneasFacturasProveedores(
    OID_LFP             INTEGER,
    cantidad            INTEGER NOT NULL,
    precioUnitario      NUMBER(5,2) NOT NULL,
    numFactura          INTEGER NOT NULL,
    OID_P               INTEGER NOT NULL,
    CONSTRAINT cantidadFacProv CHECK(cantidad > 0),
    PRIMARY KEY         (OID_LFP),
    FOREIGN KEY         (numFactura) REFERENCES FacturasProveedores,
    FOREIGN KEY         (OID_P) REFERENCES Piezas
);
sql

1 Answers

  1. Best Answer

    how are you?

    If you want to update the field via sql you can do:

    update table piezas a
set precioCompra = 
(select precioUnitario from  LíneasFacturasProveedores b
 where b.OID_P = a.OID_P)

    If you want to do something automatic, you may need to change the way the tables are designed either via trigger or stored procedure at the database level.

    Hug

    • 0