Easy way to "reset" data in whole columns to default value?

[JL]

Hi there
I have a database with entries like the following:

Id, Name, Goals_season, Goals_total
----------------------------------------
5, John, 5, 23
6, Mike, 2, 14
7, Joe, 10, 34

Every season we have to reset the "Goals_season" column to the default value
(0), but not the other columns...
What is the easiest way to do this? Should I simply delete the column and
create a new column, or...?
(I hope my question isn't to stupid - it's been a year since I worked with
mysql)
Best regards
JL

[Aggro]

JL wrote:

Hi there
I have a database with entries like the following:

Id, Name, Goals_season, Goals_total
----------------------------------------
5, John, 5, 23
6, Mike, 2, 14
7, Joe, 10, 34

Every season we have to reset the "Goals_season" column to the default value
(0), but not the other columns...
What is the easiest way to do this? Should I simply delete the column and
create a new column, or...?

update yourtablename set Goals_season=0;

[Aggro]

JL wrote:

Hi there
I have a database with entries like the following:

Id, Name, Goals_season, Goals_total
----------------------------------------
5, John, 5, 23
6, Mike, 2, 14
7, Joe, 10, 34

Every season we have to reset the "Goals_season" column to the default value
(0), but not the other columns...
What is the easiest way to do this? Should I simply delete the column and
create a new column, or...?

update yourtablename set Goals_season=0;

[JL]

update yourtablename set Goals_season=0;

Thanks for your reply.
Is update a good (efficient) way to do it if the table contains 20 columns x
500+ rows... that needs to be updated?
(the table I presented was simplified, it actually contains 20 columns that
needs to be set to 0...)
Cheers
JL

[JL]

update yourtablename set Goals_season=0;

Thanks for your reply.
Is update a good (efficient) way to do it if the table contains 20 columns x
500+ rows... that needs to be updated?
(the table I presented was simplified, it actually contains 20 columns that
needs to be set to 0...)
Cheers
JL

[Aggro]

JL wrote:

update yourtablename set Goals_season=0;

Thanks for your reply.
Is update a good (efficient) way to do it if the table contains 20 columns x
500+ rows... that needs to be updated?

The amount of columns doesn't matter AFAIK.
500 rows is nothing, you don't even need to think about speed with that
row count.

I tested it with 20 column table, with 5000 rows. It took 0.16 seconds
to complite on my old 166MHz Pentium.

mysql> update testtable set a4=0;
Query OK, 5000 rows affected (0.16 sec)
Rows matched: 5000 Changed: 5000 Warnings: 0

[Aggro]

JL wrote:

update yourtablename set Goals_season=0;

Thanks for your reply.
Is update a good (efficient) way to do it if the table contains 20 columns x
500+ rows... that needs to be updated?

The amount of columns doesn't matter AFAIK.
500 rows is nothing, you don't even need to think about speed with that
row count.

I tested it with 20 column table, with 5000 rows. It took 0.16 seconds
to complite on my old 166MHz Pentium.

mysql> update testtable set a4=0;
Query OK, 5000 rows affected (0.16 sec)
Rows matched: 5000 Changed: 5000 Warnings: 0

[Aggro]

JL wrote:

Hi there
I have a database with entries like the following:

Id, Name, Goals_season, Goals_total
----------------------------------------
5, John, 5, 23
6, Mike, 2, 14
7, Joe, 10, 34

Every season we have to reset the "Goals_season" column to the default value
(0), but not the other columns...
What is the easiest way to do this? Should I simply delete the column and
create a new column, or...?

update yourtablename set Goals_season=0;

[JL]

update yourtablename set Goals_season=0;

Thanks for your reply.
Is update a good (efficient) way to do it if the table contains 20 columns x
500+ rows... that needs to be updated?
(the table I presented was simplified, it actually contains 20 columns that
needs to be set to 0...)
Cheers
JL

[eM_gotcha]

>> Thanks for your reply.

Is update a good (efficient) way to do it if the table contains 20
columns x
500+ rows... that needs to be updated?

The amount of columns doesn't matter AFAIK.
500 rows is nothing, you don't even need to think about speed with that
row count.

yep speed really doesn't matter with that row count.
maybe you know this already, but another thing to mention:
if one column is a timestamp then mysql will automatically update this
column to the current date/time.
if you have multiple timestamp columns then only the first will be updated.
if you want to prevent this set the first timestamp to itself:
UPDATE `yourtablename` SET `Goals_season` = 0, `1_Timestamp_col` =
`1_Timestamp_col`;

i lost millions of rows because i oversight this in the mysql manual...
chris

[eM_gotcha]

>> Thanks for your reply.

Is update a good (efficient) way to do it if the table contains 20
columns x
500+ rows... that needs to be updated?

The amount of columns doesn't matter AFAIK.
500 rows is nothing, you don't even need to think about speed with that
row count.

yep speed really doesn't matter with that row count.
maybe you know this already, but another thing to mention:
if one column is a timestamp then mysql will automatically update this
column to the current date/time.
if you have multiple timestamp columns then only the first will be updated.
if you want to prevent this set the first timestamp to itself:
UPDATE `yourtablename` SET `Goals_season` = 0, `1_Timestamp_col` =
`1_Timestamp_col`;

i lost millions of rows because i oversight this in the mysql manual...
chris

[Aggro]

JL wrote:

update yourtablename set Goals_season=0;

Thanks for your reply.
Is update a good (efficient) way to do it if the table contains 20 columns x
500+ rows... that needs to be updated?

The amount of columns doesn't matter AFAIK.
500 rows is nothing, you don't even need to think about speed with that
row count.

I tested it with 20 column table, with 5000 rows. It took 0.16 seconds
to complite on my old 166MHz Pentium.

mysql> update testtable set a4=0;
Query OK, 5000 rows affected (0.16 sec)
Rows matched: 5000 Changed: 5000 Warnings: 0

[JL]

Thank you for your help
Have a nice weekend.
- JL

[JL]

Thank you for your help
Have a nice weekend.
- JL

[eM_gotcha]

>> Thanks for your reply.

Is update a good (efficient) way to do it if the table contains 20
columns x
500+ rows... that needs to be updated?

The amount of columns doesn't matter AFAIK.
500 rows is nothing, you don't even need to think about speed with that
row count.

yep speed really doesn't matter with that row count.
maybe you know this already, but another thing to mention:
if one column is a timestamp then mysql will automatically update this
column to the current date/time.
if you have multiple timestamp columns then only the first will be updated.
if you want to prevent this set the first timestamp to itself:
UPDATE `yourtablename` SET `Goals_season` = 0, `1_Timestamp_col` =
`1_Timestamp_col`;

i lost millions of rows because i oversight this in the mysql manual...
chris

[JL]

Thank you for your help
Have a nice weekend.
- JL