galvin Posted December 11, 2014 Share Posted December 11, 2014 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... Quote Link to comment Share on other sites More sharing options...
Psycho Posted December 11, 2014 Share Posted December 11, 2014 (edited) 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 Quote Link to comment Share on other sites More sharing options...
galvin Posted December 11, 2014 Author Share Posted December 11, 2014 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 Quote Link to comment Share on other sites More sharing options...
CroNiX Posted December 11, 2014 Share Posted December 11, 2014 So you're only trying to seed the db with test data and this won't be a part of the real app? Quote Link to comment Share on other sites More sharing options...
galvin Posted December 11, 2014 Author Share Posted December 11, 2014 100% accurate. Purely testing data. Quote Link to comment Share on other sites More sharing options...
Solution kicken Posted December 11, 2014 Solution Share Posted December 11, 2014 INSERT INTO table (userid, comment, weeknumber) SELECT userid, comment, 4 FROM tableThat should do it. 1 Quote Link to comment Share on other sites More sharing options...
CroNiX Posted December 11, 2014 Share Posted December 11, 2014 (edited) 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 Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.