Duplicate every row of table AND also change one field?

[galvin]

Is there a way to duplicate all rows in a table AND change one field's value for each new row?

 

For example...

 

TABLE:  comments

FIELDS: 

commentid

userid

comment

weeknumber

 

Say there are only 5 comments in there like this (I have MANY rows, but keeping sample size small for example's sake)...

 

1-17-hello-3

2-41-hey-3

3-18-yo-3

4-67-sup-3

5-12-hola-3

 

I would like to duplicate them AND make the "weeknumber" = 4, so the table ultimately has 10 rows like this...

 

1-17-hello-3

2-41-hey-3

3-18-yo-3

4-67-sup-3

5-12-hola-3

1-17-hello-4

2-41-hey-4

3-18-yo-4

4-67-sup-4

5-12-hola-4

 

Is this doable?

 

Thanks...

[Psycho]

I have to ask the obvious question: why do you need to do this? If you're duplicating everything except the weeknumber it seems there is a flaw in how you are using the data currently.

 

There is a very simple solution, but I suspect that this is a band aid for a different problem. So, I'd like to know I am not facilitating bad practices before allowing you to continue to go down the wrong path.

Edited December 11, 2014 by Psycho

[galvin]

Fair question .  I am working on a web app that is not close to going live and I'm doing a lot of testing. I have lots of records in my testing database all with the same weeknumber (since that's all i needed up to this point) but I recently added a lot of code that not only displays each weeks data, but also SUMS all data over multiple weeks.  I can't test how well that works till I have multiple records for each user.  And yes, I could duplicate one or a handful manually and test my code, but there are even more convoluted reasons that I won't bore you with that make it ideal for me to have many, if not all, records duplicated.

 

In a nutshell, it's a statistics website with lots of numbers and there will be a lot of SUMming going on, over muyltiple weeks.  Once the site is live, I will be adding all the stats every week, but for now, I just need a bunch of data over multiple weeks (whether it's the same or not) to make sure everything will work properly once I start doing that

[CroNiX]

So you're only trying to seed the db with test data and this won't be a part of the real app?

[galvin]

100% accurate. Purely testing data.

[kicken]

INSERT INTO table (userid, comment, weeknumber)

SELECT

   userid, comment, 4

FROM table

That should do it.

[CroNiX]

Not sure why you're using week number. What happens next year when the weeks start over? Best to use an actual date field imo. That will help with sorting, too, since it will sort by year/month/day, in that order, which is probably what you want when displaying comments.

Edited December 11, 2014 by CroNiX