CODEWITHSUNDEEP
sqlpostgresqlsql-update
GraceOmega
GraceOmega

Reputation: 13

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

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)

Upvotes: 0

Views: 215

Answers (2)

Jules
Jules

Reputation: 295

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

Upvotes: 2

mxix
mxix

Reputation: 3659

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.

Upvotes: 2

Related Questions