postgreSQL: use JOIN and UPDATE to update one table with data from another

Question

I'm trying to do something that I'm assuming is very simple, but it has me stumped.

I have a furniture table which includes a text column called "category" (beds, chairs, desks etc) and a categories table which pairs each of these categories, in a column called categoryname, to a categoryID (this was added at the request of a team member, to facilitate searching).

I want to populate a column in the furniture table with these categoryIDs, based on whether there's a match between the category column in the furniture table and the categoryname column in the categories table.

After searching around this site and elsewhere I decided to try this:

UPDATE furniture
SET categoryid = categories.categoryid
FROM categories
WHERE categories.categoryname = furniture.category

Which runs, but nothing happens.

I'm assuming it's because that WHERE statement isn't enough to get across what I'm trying to do and I need some kind of JOIN statement to explicitly link the categoryname column in the categories table with the category column in the furniture table, but how I do incorporate this? My best guess based on what worked for others was something like this:

UPDATE furniture
INNER JOIN categories ON categories.categoryname = furniture.category
SET furniture.categoryid = categories.categoryid
WHERE categories.categoryname = furniture.category

But it's just giving me syntax errors.

(I've checked to make sure the categoryname and category columns match in terms of spelling etc)

Check out this post: stackoverflow.com/questions/224732/…

Brian Pressler
– Brian Pressler

2015-04-21 14:33:32 +00:00
Commented Apr 21, 2015 at 14:33 — Brian Pressler
– Brian Pressler, Commented Apr 21, 2015 at 14:33
Are you using Microsoft SQL Server, or Postgres?

Aaron Bertrand
– Aaron Bertrand

2015-04-21 14:38:46 +00:00
Commented Apr 21, 2015 at 14:38 — Aaron Bertrand
– Aaron Bertrand, Commented Apr 21, 2015 at 14:38

mxix · Accepted Answer · 2015-04-21 16:50:25Z

2

Give this a try

UPDATE f
SET 
    categoryid = c.categoryid
from furniture f
INNER JOIN categories c ON c.categoryname = f.category

Could you copy again.

edited Apr 21, 2015 at 16:50

answered Apr 21, 2015 at 14:32

mxix

3,6591 gold badge18 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

GraceOmega Over a year ago

Doesn't seem to be working- relation "f" does not exist, if I get rid of the aliases it tells me that table name furniture has been specified more than once.

Jules · Accepted Answer · 2015-04-22 09:22:12Z

2

You should respect this syntax :

UPDATE furniture
SET furniture.categoryid = categories.categoryid
INNER JOIN categories ON categories.categoryname = furniture.category
WHERE categories.categoryname = furniture.category

edited Apr 22, 2015 at 9:22

answered Apr 21, 2015 at 14:34

Jules

2951 silver badge10 bronze badges

1 Comment

GraceOmega Over a year ago

I'm getting an error with this- "table name "furniture" specified more than once"

Collectives™ on Stack Overflow

postgreSQL: use JOIN and UPDATE to update one table with data from another

2 Answers 2

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related