基于字段1中的唯一值,在SQL表中多次插入同一行[英] Insert same row multiple times within SQL table based on unique value in field 1

本文是小编为大家收集整理的关于基于字段1中的唯一值,在SQL表中多次插入同一行的处理/解决方法,可以参考本文帮助大家快速定位并解决问题,中文翻译不准确的可切换到English标签页查看源文。

问题描述

本文来自:IT宝库(https://www.itbaoku.cn)

i当前有一个表,其中第1列是其余列的唯一标识符,我需要在每个唯一标识符的表中插入一个新值.示例如下:

Column 1   Column 2   Column 3 
10         Address    True 
10         City       False
10         State      True
20         Address    True
20         City       True
20         State      True

我需要根据第1列中的每个唯一标识符插入新行,例如:

Column 1   Column 2   Column 3
10         Address    True
10         City       False
10         State      True
*10         NEW        NEW*
20         Address    True
20         City       True
20         State      True
*20         NEW        NEW*

出于某种原因,SQL脚本快速查找和插入只是在星期一早上逃脱了我.任何帮助将不胜感激.

谢谢!

推荐答案

insert into table-name
(Column1, Column2, Column3)
select Column1, 'NEW', 'NEW*' from table-name group by Column1

您可以在子选项中使用独特的使用,但是我已经开始切换到组,如果我想更改查询以计数或其他内容,这会更加灵活.

其他推荐答案

您可以尝试Marlin Pierce的建议的变体:

INSERT INTO [table-name]
([Column 1], [Column 2], [Column 3])
SELECT [Column 1], 'NEW', 'NEW*' from [table-name] where [Column 2] = 'Address' group by [Column 1];

这将为您现有的每个行创建一个新行.

请参阅 sqlfiddle 有关可执行的示例.

本文地址:https://www.itbaoku.cn/post/2786892.html

问题描述

I currently have a table where column 1 is a unique identifier for the remaining columns and I need to insert a new value into the table for each unique identifier. The example is as follows:

Column 1   Column 2   Column 3 
10         Address    True 
10         City       False
10         State      True
20         Address    True
20         City       True
20         State      True

I need to insert a new row based upon each unique identifier in Column 1, like so:

Column 1   Column 2   Column 3
10         Address    True
10         City       False
10         State      True
*10         NEW        NEW*
20         Address    True
20         City       True
20         State      True
*20         NEW        NEW*

For some reason, the SQL script for the quick lookup and insert is just escaping me on a Monday morning. Any help would be appreciated.

Thanks!

推荐答案

insert into table-name
(Column1, Column2, Column3)
select Column1, 'NEW', 'NEW*' from table-name group by Column1

You can alternatively use a distinct in the sub-select, but I have started switching to group by, which can be more flexible if I want to change a query to count or something.

其他推荐答案

You can try at variant of Marlin Pierce's suggestion of:

INSERT INTO [table-name]
([Column 1], [Column 2], [Column 3])
SELECT [Column 1], 'NEW', 'NEW*' from [table-name] where [Column 2] = 'Address' group by [Column 1];

This creates a single new row for each of your existing rows.

See the SqlFiddle for executable sample.