SQL 如何将数据插入到其他列中没有 NULL 的特定列中？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/30306977/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to INSERT data into a specific column without NULLs in the other columns?
提问by Arsenii
I have a table ("table1") with 3 columns called col1, col2 and col3 (each is VARCHAR) with 4 values as shown below:
我有一个表（“table1”），其中包含 3 个名为 col1、col2 和 col3（每个都是 VARCHAR）的列，其中包含 4 个值，如下所示：
col1 col2 col3
datA1 datB1 datC1
datA2
I need an ability to add a data at any time into any column NOT affecting on the other ones. The very popular code in the Internet is that (say, we need to add data only to the columns col2 and col3):
我需要能够随时将数据添加到不影响其他列的任何列中。网上很流行的代码是这样的（比如说，我们只需要在col2和col3列中添加数据）：
INSERT INTO table1 (col2, col3)
VALUES ('datB2', 'datC2');
But it adds new rows, like below:
但它添加了新行，如下所示：
col1 col2 col3
datA1 datB1 datC1
datA2
NULL datB2 datC2
What I really need is to fill the row starting with value "datA2" at the column "col1" with new values and get the table like below:
我真正需要的是用新值填充列“col1”中以值“datA2”开头的行，并得到如下表：
col1 col2 col3
datA1 datB1 datC1
datA2 datB2 datC2
If someone could help me I would be highly appreciated!!! Thanks. Arsenii.
如果有人可以帮助我，我将不胜感激！！！谢谢。阿塞尼。
Update: The table has 3 columns and each column responses for a particular type of values (say: name, colour, size). What I need is just a possibility to add new values at any time in a particular column and have them without Null and new rows if it has a free cell before.
更新：该表有 3 列，每列响应特定类型的值（例如：名称、颜色、大小）。我需要的只是一种在特定列中随时添加新值的可能性，并且如果之前有空闲单元格，则它们没有 Null 和新行。
采纳答案by Arsenii
I found the solution (a chain of logical operations):
我找到了解决方案（一系列逻辑运算）：
1) CHECKif there is a cell (in the target column) with values either ""or NULL.
1)CHECK如果有一个单元格（在目标列中）的值为""或NULL。
2) IFit has one of those then rewrite the FIRST one keeping the values of the other cells in this row at their places (assumably we use UPDATE))) ).
2）IF它有一个然后重写第一个，将这一行中其他单元格的值保留在它们的位置（假设我们使用UPDATE））））。
3) ELSEjust add a new row with all NULLs in the other cell in the row.
3）ELSE只需NULL在该行的另一个单元格中添加一个包含所有s的新行。
If we want to add a few values into various columns simultaneously we may prepare our queries for all of those and then execute them simultaneously (sorry for tautology).
如果我们想同时将一些值添加到不同的列中，我们可以为所有这些准备我们的查询，然后同时执行它们（对不起，重言式）。
If we need to add a few values into the same column within one query we can prepare it, using loops (repeating paragraphs 1 and 2 (or, optionally, 3).
如果我们需要在一个查询中将几个值添加到同一列中，我们可以使用循环（重复第 1 段和第 2 段（或可选地，第 3 段））来准备它。
回答by parth bahuguna.
UPDATE table1
SET col2 = dataB2, col3 = dataC2
WHERE col1 = dataA2;
This may serve your purpose :)
这可能符合您的目的:)
回答by John Odom
You will have to use the UPDATEstatement if you want to add data to an existing row. Like this for example:
UPDATE如果要将数据添加到现有行，则必须使用该语句。像这样例如：
UPDATE table1 SET
col2 = 'data5'
col3 = 'data6'
FROM table1
WHERE col1 = 'data4'
Also it does look like the root of your problem is poor database design, but this query is just to show you how to add data to an existing row.
此外，看起来您的问题的根源确实是糟糕的数据库设计，但此查询只是为了向您展示如何将数据添加到现有行。
回答by Kushagra
Suppose you have the table
CLIENT_MASTER
假设您有CLIENT_MASTER表
ClientNo Name
C00001 Ivan
C00002 Himanshu
客户No Name
C00001 Ivan
C00002 Himanshu
Now you add a new column City
现在您添加一个新列 City
ALTER table CLIENT_MASTER
ADD( City varchar(10));
Now if you want to add values in already existing rows you can use UPDATE command.
For example
现在，如果您想在现有行中添加值，您可以使用 UPDATE 命令。
例如
UPDATE CLIENT_MASTER
SET City='Delhi'
WHERE ClientNo='C00001';
The updated table is
ClientNo Name City
C00001 Ivan Delhi
C00002 Himanshu NULL
更新后的表是
ClientNo Name City
C00001 Ivan Delhi
C00002 Himanshu NULL
回答by paul
Given a table structure, with two data rows:
给定一个表结构，有两个数据行：
key value
--------------------
team accounts
manager jeff
Each time you want to change a value, you need to check whether it is already there (for update), or not (for insert). So, to change the value of the managerproperty:
每次要更改值时，都需要检查它是否已经存在（用于更新），或不存在（用于插入）。因此，要更改manager属性的值：
if exists(select * from keyValues where key = 'manager')
update keyValues set value = 'mike' where key = 'manager'
else
insert into keyValues ('manager', 'mike')
回答by akesh
Use this:
用这个：
INSERT INTO table1 (col2, col3)
VALUES ('datB2', 'datC2')
WHERE col1 = datA2;
回答by Raju
Use this, if value nullthen insert empty value.
使用这个，如果值null然后插入空值。
$cal1=$cal1 ? "data5" : '';
$cal2=$cal2 ? "data6" : '';
INSERT INTO table1 (col2, col3)
VALUES ("'.$cal1.'", "'.$cal2.'");
