mysql: most DRY row copy - Printable Version +- CodeIgniter Forums (https://forum.codeigniter.com) +-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20) +--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23) +--- Thread: mysql: most DRY row copy (/showthread.php?tid=6652) |
mysql: most DRY row copy - El Forum - 03-06-2008 [eluser]gungbao[/eluser] imagine a database table with an auto_incremented primary "id" and lets say 1000 other columns, which are not auto_incremented. what would be the most efficient and most DRY method to clone a row of such a table. $query = $this->db->get_where('mytable', array('id' => $id)); $data = $query->row(); $data->id = ''; // perhaps more clean code to say $data->id =NULL; $db->insert= $this->db->insert('mytable', $data); will do the job. But that causes two querys. I want to to it in ONE query. Code: $SQL = "INSERT INTO mytable SELECT * FROM mytable WHERE id=$id"; Okay, that won't work because of the uniqueness of the primary key. I could manually type all column names from 1 to 999 but leave out column 0 or could retrieve tables meta data and even cache it, but I am keen to know if there is a maximum performant sequel. Example: Code: $SQL = "INSERT INTO mytable SELECT col_1, col_2, ... , col_999 FROM mytable WHERE id=$id"; This would work but is it DRY? No, for sure. Any Guru out there to find a cool solution??? Greetings, Christoph mysql: most DRY row copy - El Forum - 03-07-2008 [eluser]Sean Murphy[/eluser] Here's an idea: make a copy of mytable and make the id column null for all rows. Then just do your INSERT/SELECT. Code: $SQL = “INSER INTO mytable SELECT * FROM mytable_copy WHERE other_col=$other_col”; Your first solution seems to be the best, but to cut down on queries you could select all or chunks of rows from the first table instead of one at a time. mysql: most DRY row copy - El Forum - 04-04-2012 [eluser]thocutt1020[/eluser] This uses more queries but it works - I throw this into my model and boom I can copy records no problem. Adapted from a quick n dirty function I found here: PHP MySQL Copy Record Function Code: function copy_record($id,$table='products', $id_field='product_id') { mysql: most DRY row copy - El Forum - 04-09-2012 [eluser]gungbao[/eluser] My Question (for some good reasons) was: Howto do it in MySQL only! I guess the solution could be found in the expression of in "INSERT ... ON DUPLLICATE KEY expression;" perhaps combined with LAST_INSERT_ID()? Still wondering how mysql does not easily support such a common problem more flexible, there must be a solution. Cheers Christoph mysql: most DRY row copy - El Forum - 04-10-2012 [eluser]jmadsen[/eluser] Best I've been able to manage so far is to run this: Code: SELECT inside a stored procedure, then execute that. my testing was database: test table: category primary key: category_id You can cheat a little and use "AND EXTRA <> 'auto_increment' " in your where clauses instead of the REPLACE, assuming that is correct for your table. Toying with some other ideas, but I think in the end you'll need a stored procedure to do this |