2

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.

Improve this question
6
  • 1
    Use the second method, but get rid of the single quotes. You should probably be storing the data with one row per id and month along with the value. Trying to work with multiple columns as an array is usually a bad idea. Commented Sep 27, 2015 at 15:23
  • You can export your tables values, apply "logic" with php and after that import it again Commented Sep 27, 2015 at 15:23
  • @GordonLinoff It's better than having multiple tables for each month. The table is about sold products by employees. So id is numer of the product and column is how many products they sold in that day Commented Sep 27, 2015 at 15:29
  • @MateuszSowiński . . . Sounds like a table that should have about three columns (well, I would have others, such as create date and an auto-incrementing id). Commented Sep 27, 2015 at 15:52
  • @GordonLinoff Could you tell what 3 columns? like id, employee, date, amount, name of product? Commented Sep 27, 2015 at 17:31

1 Answer 1

Reset to default
2

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.

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

2 Comments

That's what I was intending with the 'units' column. You could call it 'products'. Or have I miss-understood your use case?
I meant that i have many products... should i do it like column: product name, and column amount?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.