Update multiple rows with different values

Question

I have these 3 tables

Table books (i want to fill categories column with 1 string of categories delimited with a comma)

id      categories
1       null
2       null
3       null
...     ...

Table categories

id      name
1       adventure
2       horror
...     ...

Table BooksCategories (a book can have multiple categories)

bookid    categoryid
1         1
1         3
2         2
3         1
3         2
...       ...

I have a query that gives me something like this

bookid      categories
1           horror, adventure
2           action,...,...

Now, I want to update the column categories of my books table to the corresponding value of my query. Is it possible to do that in an update?

Thanks, I hope it is clear enough

Please re-consider your design: what you want to achieve is a presentation layer that could - and should, in most cases - be computed at runtime, or in specific views. Don't store redundant, consistance-dangerous data: you will then need quite a lot of triggering to keep the "categories" column up do date. — LittleSweetSeas
– LittleSweetSeas, Commented Sep 13, 2013 at 13:12

John Woo · Accepted Answer · 2013-09-13 13:13:11Z

4

You can JOIN your query with table books so you can update the column,

UPDATE  a
SET     a.categories = b.categories
FROM    books a
        INNER JOIN
        (
            -- paste your query here
            -- SELECT   bookid, categories,.....
        ) b ON  a.id = b.bookid

answered Sep 13, 2013 at 13:13

John Woo

265k70 gold badges509 silver badges500 bronze badges

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

Collectives™ on Stack Overflow

Update multiple rows with different values

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related