CODEWITHSUNDEEP
phpmysqlregex
user5306544
user5306544

Reputation:

MySQL using RegEx to update/select columns

I searched in the internet for an answer to select every columns that matches regex pattern. I didn't find one, or maybe I did, but I didin't understand it, because I'm new to DataBases. So here's the sql I was trying to run:

UPDATE `bartosz` SET 'd%%-%%-15'=1

(I know it's bad)

I have columns like: ID | d1-1-15 | d2-1-15 | d3-1-15 | d4-1-15 ... (for 5 years, every month, and day)

So is there a way to select all columns from 2015? I know i can loop it in php so the sql would look like:

UPDATE `bartosz` SET 'd1-1-15'=1, 'd1-1-15'=1, 'd3-1-15'=1 [...]

But it would be really long.

Upvotes: 2

Views: 106

Answers (1)

Steve E.
Steve E.

Reputation: 9343

Strongly consider changing your approach. It may be technically possible to have a table with 2000 columns, but you are not using MySQL in a way that gets the most out of the available features such as DATE handling. The below table structure will give better flexibility and scaling in most use cases.

Look into tables with key=>value attributes.

id   employee    date         units
1    james       2015-01-01   2
2    bob         2015-01-01   3
3    james       2015-01-02   6
4    bob         2015-01-02   4

With the above it is possible to write queries without needing to insert hundreds of column names. It will also easily scale beyond 5 years without needing to ALTER the table. Use the DATE column type so you can easily query by date ranges. Also learn how to use INDEXes so you can put a UNIQUE index on the employee and date fields to prevent duplication.

Upvotes: 2

Related Questions