SQL：如何使用预定义的值集更新空列

I have a table with, let's say, 100 records. The table has two columns. The first column (A) has unique values. The second column (B) has NULL values

我有一张桌子,比方说有100条记录。该表有两列。第一列(A)具有唯一值。第二列(B)具有NULL值

For 4 elements from column A I'd like to associate some earlier defined values, and they are unique as well.

对于A列中的4个元素,我想关联一些早先定义的值,它们也是唯一的。

I don't care about which value from column B will be associated with the value from column A. I'd like to associate 4 unique values with another 4 unique values. Basically, like I'd cut and paste a block of values from one column to another in excel.

我不关心B列中的哪个值与A列中的值相关联。我想将4个唯一值与另外4个唯一值相关联。基本上,就像我在excel中将一列值从一列剪切并粘贴到另一列。

How can I do it without using cursors?

如何在不使用游标的情况下执行此操作?

I'd like to use one Update statement for ALL rows instead one Update statement for EVERY row as I do now.

我想为所有行使用一个Update语句,而不是像现在一样对每行使用一个Update语句。

3 个解决方案

#1

Try this:

UPDATE t
SET ColumnB = BValue
FROM Table t 
INNER JOIN
(
SELECT 1 AValue, 'Mouse' BValue UNION
SELECT 2, 'Cat' UNION
SELECT 3, 'Dog' UNION
SELECT 4, 'Wolf' 
) PreDefined ON(t.ColumnA = PreDefined.AValue)

Use any number you want in the 'PreDefined' table, as long as they are unique and within the range of values in columnA of your original table.

在“预定义”表中使用您想要的任何数字,只要它们是唯一的并且在原始表的columnA中的值范围内。

#2

If you are only trying to fill a table for testing purposes, I guess you could:

如果您只是为了测试目的而填写表格,我猜您可以:

A) Use the value from Column A itself (as it is already unique).

A)使用A列本身的值(因为它已经是唯一的)。

B) If they are to be different, use some function on the column A's value to obtain a column B value (something simple, like (ColumnA * 10), and this would give youA)

B)如果它们不同,请在A列的值上使用一些函数来获得B列值(简单,如(ColumnA * 10),这样就可以得到A)

C) Create a temp table with a "dictionary" setting a B value for each possible A value, and then update the rows desired on your table looking up from values on this dictionary table.

C)创建一个临时表,其中“字典”为每个可能的A值设置B值,然后根据此字典表中的值查找表中所需的行。

Anyway, if you explain a little further your purpose it will be easier to try suggesting you a solution.

无论如何,如果你进一步解释一下你的目的,那么尝试建议你一个解决方案会更容易。

#3

if your animal data is already in a database table, then you can use a single update statement like this:

如果您的动物数据已经在数据库表中,那么您可以使用如下的单个更新语句:

update target_table t4 
set columnb = (
    select animal_name 
    from (select columna, animal_name 
            from (select rownum rowNumber, animal_name from animal_table) t1
                join (select rownum rowNumber, columna from target_table t1 where columnb is null) t2
                on t1.rowNumber = t2.rowNumber
            ) t3 
    where t4.columna = t3.columna
    )
;

this works by selecting a sequence number and animal name from the source table, then selecting a sequence number and columna value from your target table. by joining those records on the sequence number you guarantee you get exactly 1 animal name for each columna value. you can then join those columna-to-animal records to your target table to do an update of columnb.

这可以通过从源表中选择序列号和动物名称,然后从目标表中选择序列号和columna值来实现。通过在序列号上加入这些记录,您可以保证每个columna值都能得到1个动物名称。然后,您可以将这些columna-to-animal记录加入目标表以更新columnb。

for more background on updating one table from values in another, you might consider the solutions presented here: Update rows in one table with data from another table based on one column in each being equal. the only difference is that in your example, you do not have any column that matches between your target table and your animal names table, so you need to use the rownum to create an arbitrary 1-to-1 matching of records.

有关从另一个表中的值更新一个表的更多背景信息,您可以考虑此处提供的解决方案:使用另一个表中的数据更新一个表中的行,基于每个表中的一个列相等。唯一的区别是,在您的示例中,您没有任何匹配目标表和动物名称表的列,因此您需要使用rownum创建任意的1对1记录匹配。

if your unique options are in a text file or spreadsheet, then you can format them into a fixed-width space-padded string and pick the one you want using the rownum index like so:

如果您的唯一选项位于文本文件或电子表格中,那么您可以将它们格式化为固定宽度的空格填充字符串,并使用rownum索引选择所需的字符串,如下所示:

update table_name 
set columnb = trim(substr('mouse cat   dog   wolf  ', rownum*6-6, 6)) 
where columnb is null;