0

Hello I have 2 tables in PostgreSQL: the 1st one

"Products" 
id
price
factor1
factor2

the columns factor1 and factor2 they both are empty and I want to populate them with the data of another table

"Article" 
id
factor1
factor2

I want to populate the columns where the id of Product is the same as the Article but the tables doesn't have a relationship to each other or FK so how can I accomplish this?

I've tried this:

UPDATE Products
SET factor1 =
  (SELECT factor1
   FROM Article
   WHERE Article.id = Products.id)
WHERE EXISTS
  (SELECT factor1
   FROM Article
   WHERE Article.factor1 = Products.id)

but I get this error:

SQL Error [42P01]: ERROR: relation "Article" does not exist Position: 213

  • works for me. Or at least, doesn't give the error you indicate, as long as the table "article" does exist, but I doubt it does what you want since you have two different where clauses. – jjanes Sep 24 at 19:48
  • @jjanes you think it might be a DBeaver error? – Carlos Delgado Sep 24 at 20:00
  • I doubt it. It looks just like a PostgreSQL error which dbeaver is passing along to you. It could be that dbeaver is setting your search_path in such a way that "article" can't be found. Can you run the statement in psql to take dbeaver out of the loop and see what happens? – jjanes Sep 24 at 20:07
  • 2
    "Article" is a different name then Article - the error message indicates you used "Article" not Article as the code in your question does – a_horse_with_no_name Sep 24 at 20:41
0

The more compact way to write this would be:

UPDATE Products
SET factor1 = article.factor1
from Article
WHERE Article.id = Products.id and products.factor1 is null
  • can you explain the where clause why products.factor1 is null ? – Carlos Delgado Sep 24 at 19:59
  • The IS NULL is just to not update it if it is already populated. If you are certain the column is completely empty, or want to overwrite them even if they are already populated, you can leave that part out. – jjanes Sep 24 at 20:08
0

As @a_horse_with_no_name has pointed out in comments:

"Article" is a different name then Article

A fiddle with you current table names & query:

Example Using double-quoted names

Returns your error:

ERROR: relation "products" does not exist LINE 1:
UPDATE Products

But if you un-quote it:

Example without double-quoting names

it works fine.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.