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: http://stackoverflow.com/questions/224732/sql-update-from-one-table-to-another-based-on-a-id-match?rq=1 — Brian Pressler, Apr 21 '15 at 14:33

mxix · Answer 1 · 2015-04-21T16:50:25.793

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 '15 at 16:50

answered Apr 21 '15 at 14:32

mxix

3,539
1
16
23

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. – GraceOmega Apr 21 '15 at 16:33

Jules · Answer 2 · 2015-04-22T09:22:12.597

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 '15 at 09:22

answered Apr 21 '15 at 14:34

Jules

295
1
10

I'm getting an error with this- "table name "furniture" specified more than once" – GraceOmega Apr 21 '15 at 16:30

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

2 Answers2