Copying rows to MySQL

存储架构 2017-12-30

I want to copy all of the columns of a row, but not have to specify every column. I am aware of the syntax at http://dev.mysql.com/doc/refman/5.1/en/insert-select.html
but I see no way to ignore a column.

For my example, I am trying to copy all the columns of a row to a new row, except for the primary key.

Is there a way to do that without having to write the query with every field in it?

If your id
or primary key column is an auto_increment
you can use a temp table:

CREATE TEMPORARY TABLE temp_table
AS
SELECT * FROM source_table WHERE id='7';
UPDATE temp_table SET id='100' WHERE id='7';
INSERT INTO source_table SELECT * FROM temp_table;
DROP TEMPORARY TABLE temp_table;

so in this way you can copy all data in row id='7' and then assign new value '100' (or whatever value falls above the range of your current auto_increment value in source_table).

Edit: Mind the ; after the statments 🙂

Hello, buddy!

责编内容by:Hello, buddy! (源链)。感谢您的支持!

您可能感兴趣的

Why Thread May Hang in “Waiting for table le... Last time I had to use gdb to check what's going on in MySQL server and found something useful with it to share in theblog post it was April 2017,...
How do I get a list of users? i have 3 tables in mysql.. i am using mysqli User - userid - username - email Site_A - u...
MYSQL 1064 error in select clause COALESCE function in the SELECT clause Problem: is to find the last Non NULL value from the table based on id order, found here ...
go-ycsb:一个 Go 的 YCSB 移植 YCSB 是一个非常出名的性能测试框架,我们可以非常方便的用它来对系统进行多维度的性能测试,本来我也准备使用它来对我们系统进行性能测试的,但在调研了一番之后,我决定直接用 Go 来完全移植一个。过年的时候就一直在干这件事情,于是就有了 go-ycsb 。 为什么需要 YCSB? 先来说说...
PHP deletes MySQL records older than 3 days I have the following at the top of every page so when the website is loaded by anyone PHP deletes any record in a specific database table that is olde...