UPDATE multiple MySQL rows with different values

Question

Note: I realize this may be confusing taking about tables and columns below so here is a simplified version of the two tables I mention:

Table "categories" has columns: id, type

Table "entries" has columns: id, categories, ...

I have a MySQL table named entries where one of the columns, named categories, is a string of pipe separated indices. For example, "|1|2|3|" might be a possible value and "|1|3|4|" could be another. The numbers represent indices of the table categories.

I'm writing an admin script that allows a new row to be inserted into the categories table and when doing this, the appropriate rows of the table entires should have the categories column updated. For example, if a newly inserted row of the categories table has index 5 then "5|" should be concatenated to each appropriate column categories of the entries table.

I realize that I could could use UPDATE per appropriate entries row when adding a new category but am wondering if there is a better way to go about this. In case this is not clear I know that this is an option but want to know if this can be made into one statement (pseudo-code):

foreach ($entriesToUpdate as $currEntry)
"UPDATE entires SET categories='".$currValue."|".$newIndex."' WHERE id=".$currId;

I sincerely hope you don't go ahead with that database design and regret it when you site gets big and your server goes down. That's not a good database schema at all — Hanky Panky
– Hanky Panky, Commented May 17, 2014 at 6:35
Regular SQL can't do what you want. You should have an additional table, where instead of |1|2|3|, in the additional table you would have 3 rows, each with one field which would be either 1, 2, or 3, and a second field which would have the ID of the row in the first table — Jayy
– Jayy, Commented May 17, 2014 at 6:37
@Jodes, I don't follow. Maybe it is worth pointing out that each entry may have 0 or more categories? — asimes
– asimes, Commented May 17, 2014 at 6:42
You have to add a table ENTIRES_CATEGORIES(entries_id, categories_id) because entries and categories are in a many to many relationship. — kiks73
– kiks73, Commented May 17, 2014 at 6:48
@kiks73 there's no reason to add a third field to the many-to-many table - just use the two fields as a composite primary key. — Amber
– Amber, Commented May 17, 2014 at 6:49

Amber · Accepted Answer · 2014-05-17 06:48:28Z

2

This can be done with an expression-based update:

UPDATE entries SET categories=CONCAT(categories, "5|") WHERE id IN (1,2,3,4,...)

(5| instead of 5| from your example, since your examples seem to show that the existing value will start and end with |s already.)

That said, you'd probably be better off with a database schema that stores a mapping of entries to categories in a separate many-to-many table. For example:

categories:

id | type | ...
---------------
1    ...
2    ...
3    ...

entries:

id  | ...
---------
100   ...
101   ...
102   ...

entries_to_categories:

entry | category
----------------
100     1
100     2
101     3

Then you can use JOINs to retrieve the set of categories if desired, or check if something is in a category. In this case, entry 100 is in categories 1 and 2, entry 101 is in category 3, and entry 102 is in no categories.

edited May 17, 2014 at 6:48

answered May 17, 2014 at 6:43

Amber

531k89 gold badges643 silver badges558 bronze badges

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

1 Comment

asimes Over a year ago

This does seem like a good idea, I'll look into restructuring the tables / code. Thank you for posting the MySQL command as well

Collectives™ on Stack Overflow

UPDATE multiple MySQL rows with different values

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related