复制同一个表中的行并仅更新 MySQL 中的一列

发布于 2024-10-06 17:46:12 字数 305 浏览 6 评论 0原文

我需要根据某些条件(where 子句)从表中复制一行,在同一个表中创建重复条目并仅更新一列(外键)。但是因为“select * from Table_name”返回包括primary_key在内的所有字段,所以无法插入新行 错误:重复的键。

我不知道表的所有列,所以我不能通过在选择查询中仅给出所需的列名来忽略primary_key。

有什么方法可以忽略 Primary_key 本身的获取吗?或者我可以获取整行,并将 Primary_key 值设置为 null,然后将该行插入表中,以便它自动增加要添加的新行的 Primary_key 。

谢谢。

I need to copy a row based on some condition(where clause), from a table,make a duplicate entry in same table and update just one column(a foreign_key). But because "select * from Table_name" returns all fields including primary_key it is not able to insert new row
error : duplicate key.

I don't know all the columns of table, so i cant ignore primary_key by giving only required column names in select query.

Is there any way to ignore primary_key from fetching itself.. or can i fetch the whole row, and set the primary_key value to null, and then insert the row in table, so that it auto increments the primary_key for new row being added.

Thanks.

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(3

若沐 2024-10-13 17:46:12

通过查询信息模式表并使用动态 SQL 构建适当的语句,可以执行您想要的操作。但是,您应该非常小心地使用这种方法,因为修改您不知道列名的表中的数据可能会导致各种问题:

DROP TABLE mytable;

CREATE TABLE `mytable` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `col1` varchar(255) DEFAULT NULL,
  `col2` varchar(255) DEFAULT NULL,
  `col3` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

INSERT INTO mytable (col1, col2, col3) VALUES ('A', 'B', 'C');

SELECT * FROM mytable;

+----+------+------+------+
| id | col1 | col2 | col3 |
+----+------+------+------+
|  1 | A    | B    | C    |
+----+------+------+------+

SET @columns := (
  SELECT
    GROUP_CONCAT(column_name)
    FROM information_schema.columns
    WHERE table_schema = 'test'
    AND table_name = 'mytable'
    AND column_key <> 'PRI'
);

SET @sql := (
  SELECT CONCAT(
    'INSERT INTO mytable (', @columns, ') ',
    'SELECT ', @columns, ' FROM mytable ',
    'WHERE id = 1;'
  )
);

SELECT @sql;

+---------------------------------------------------------------------------------------+
| @sql                                                                                  |
+---------------------------------------------------------------------------------------+
| INSERT INTO mytable (col1,col2,col3) SELECT col1,col2,col3 FROM mytable WHERE id = 1; |
+---------------------------------------------------------------------------------------+

PREPARE stmt FROM @sql;

EXECUTE stmt;

SELECT * FROM mytable;

+----+------+------+------+
| id | col1 | col2 | col3 |
+----+------+------+------+
|  1 | A    | B    | C    |
|  2 | A    | B    | C    |
+----+------+------+------+

It is possible to do what you want by querying the information schema tables, and using dynamic SQL to build an appropriate statement. However, you should take great care with this approach, as modifying data in tables for which you do not know the column names could result all sorts of problems:

DROP TABLE mytable;

CREATE TABLE `mytable` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `col1` varchar(255) DEFAULT NULL,
  `col2` varchar(255) DEFAULT NULL,
  `col3` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

INSERT INTO mytable (col1, col2, col3) VALUES ('A', 'B', 'C');

SELECT * FROM mytable;

+----+------+------+------+
| id | col1 | col2 | col3 |
+----+------+------+------+
|  1 | A    | B    | C    |
+----+------+------+------+

SET @columns := (
  SELECT
    GROUP_CONCAT(column_name)
    FROM information_schema.columns
    WHERE table_schema = 'test'
    AND table_name = 'mytable'
    AND column_key <> 'PRI'
);

SET @sql := (
  SELECT CONCAT(
    'INSERT INTO mytable (', @columns, ') ',
    'SELECT ', @columns, ' FROM mytable ',
    'WHERE id = 1;'
  )
);

SELECT @sql;

+---------------------------------------------------------------------------------------+
| @sql                                                                                  |
+---------------------------------------------------------------------------------------+
| INSERT INTO mytable (col1,col2,col3) SELECT col1,col2,col3 FROM mytable WHERE id = 1; |
+---------------------------------------------------------------------------------------+

PREPARE stmt FROM @sql;

EXECUTE stmt;

SELECT * FROM mytable;

+----+------+------+------+
| id | col1 | col2 | col3 |
+----+------+------+------+
|  1 | A    | B    | C    |
|  2 | A    | B    | C    |
+----+------+------+------+
绮烟 2024-10-13 17:46:12

如果您了解所获得的数组的序列,那么在设置某种条件后,您可以忽略具有主键的字段。

像这样检查它是否有效

谢谢。

if you have knowledge about the sequences of the array you got, then after putting some kind of condition you can ignore field which is having primary key.

Check like this if it works

Thanks.

好久不见√ 2024-10-13 17:46:12

为什么你知道键是什么,但不知道其他列是什么?如果情况确实如此,那么我认为您必须编写一些动态 SQL - 或者更好的是,将任务交给确实了解表结构的人。

How can it be that you know what the key is but don't know what the other columns are? If that's really the case then I think you'll have to write some dynamic SQL - or better still, give the task to someone who does know the table structure.

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文