Home / Questions / 502515
Accepted
AlejandroR
Asked: 2022-05-11 04:21:35 +0800 CST 2022-05-11 04:21:35 +0800 CST

Can anyone help me with this postgres query?

  • 772

I am trying to load information from different tables into one. I have the following code:

UPDATE coche ch
INNER JOIN modelo mo ON mo.id = ch.codmodelo
INNER JOIN color co ON co.idmodelo = ch.codmodelo
INNER JOIN matricula ma ON ma.idmodelo = ch.codmodelo
SET ch.codmodelo = mo.id, ch.codcolor=co.id, ch.codmatricula=ma.id
WHERE mo.id >0

My intention is to update the fields of the cars table, with a specific field from the model, color and license plate tables.

If someone can help me to understand how to do it. What I have found on the internet or asked has not helped me to solve it. Thanks in advance

postgresql

1 Answers

  1. Best Answer

    To simulate JOINin a UPDATEyou must use FROM, I recommend the official documentation

    for instance:

    UPDATE orderlines o set quantity =1, orderdate =current_Date + c.age  
FROM orders as oo, customers  as c  
WHERE
o.orderid = oo.orderid AND 
oo.customerid =c.customerid  AND 
c.country ='US'

    and in your case it would be something like this:

    UPDATE coche ch
SET codmodelo = mo.id, codcolor=co.id, codmatricula=ma.id

FROM modelo as mo,color as co, matricula as ma 
WHERE
mo.id = ch.codmodelo AND 
co.idmodelo = ch.codmodelo AND
ma.idmodelo = ch.codmodelo AND
mo.id >0
    • 1