Update sql with join

Question

Update sql with join

Navigation

#1 by (0 votes)
#2 by (-1 votes)

1

I am trying to make update where I would need to join to join two tables.

I have the table ADJUDICACION and the table PROVEEDOR , in which the join would be: ADJUDICACION.ID_PROVEEDOR = PROVEEDOR.ID . And what I wanted was that ADJUDICACION.ID_ESTADO = 701 when PROVEEDOR.ID_TIPO_PROVEEDOR = 103 . I tried to do several things similar to this one but nothing:

    UPDATE ADJUDICACION SET ADJUDICACION.ID_ESTADO = 701
    FROM ADJUDICACION
    JOIN PROVEEDOR ON ADJUDICACION.ID_PROVEEDOR = PROVEEDOR.ID
    WHERE PROVEEDOR.PRO_ID_TIPO_PROVEEDOR = 103;

sql oracle

asked by Richard Sánchez Coliao 08.08.2018 в 12:04

source

2 answers

I can not create a calendar that syncs with Google Magento 2, how to know the names of the blocks?

score 0 · Answer 1

Another way that can be optimal, easy to write and maintain would be this

update ADJUDICACION 
   set ID_ESTADO = 701
 where ID_PROVEEDOR in (select p.ID
                          form PROVEEDOR p
                         where p.PRO_ID_TIPO_PROVEEDOR = 103);

score -1 · Answer 2

To do an Update with a join in ORACLE, you can do it in a way:

UPDATE 
(SELECT table1.value as OLD, table2.CODE as NEW
 FROM table1
 INNER JOIN table2
 ON table1.value = table2.DESC
 WHERE table1.UPDATETYPE='blah'
) t
SET t.OLD = t.NEW

But as in your code you will not insert the value of another table, you could do it:

UPDATE ADJUDICACION a
   SET a.ID_ESTADO = 701
 WHERE EXISTS 
       (SELECT NULL
          FROM ADJUDICACION ad
         INNER JOIN PROVEEDOR p ON ad.ID_PROVEEDOR = p.ID
         WHERE ad.ID_PROVEEDOR = a.ID_PROVEEDOR
           AND p.PRO_ID_TIPO_PROVEEDOR = 103)

I found this information in this answer and in this