Result update from another table

Asked

Viewed 105 times

2

I have the following SQL: 

SELECT id_grade
      ,MAX(data_lancamento)
  FROM faturamento_produtos
 WHERE data_lancamento < '01-01-2010'
 GROUP BY id_grade

Where it only searches for what was released before 2010.

I have another table that I want to change a field according to the result of this SELECT.

In short:
I want to put the column quantidade_estoque = 0 table produto_estoque for what had a release date prior to 2010.

Both tables have the same field id_grade.

sql postgresql

  • Have you tried JOIN?

  • @Marconciliosouza, I’ll take a look, but you know if it would work in postgres?

  • In postgres, I do not know. I had not seen that it was this bank.

1 answer

1

See if that’s what I understand...

update pq
set quantidade_estoque = 0
from produto_estoque pq
join (
        SELECT id_grade,max(data_lancamento) 
        FROM faturamento_produtos 
        where data_lancamento < '01-01-2010' 
        group by id_grade
     )fp
on fp.id_grade = pq.id_grade

  • I don’t know in the newer versions of postgres, if I’m not mistaken about the 9.0 backwards is just like this http://stackoverflow.com/a/7869611/1342547

  • @rray Ups, I didn’t realize it was postgres, this solution would be for sql server

  • 1

    :P it is strange to use ansi 89 sintex for Join.

  • Unfortunately it does not work in postgresql itself, the following error occurs: relation pq does not exist.

Browser other questions tagged sql postgresql

You are not signed in. Login or sign up in order to post.