SQL新手，需要查询帮助

I have a database table of my own that I am trying to construct a query for that seems simple enough, but I feel like I am messing up somewhere because the results are not what they should be.

我有一个我自己的数据库表,我试图构建一个看起来很简单的查询,但我觉得我在某处弄乱,因为结果不是它们应该是什么。

I basically have a table that is like the following:

我基本上有一个如下表:

Table: Data

Columns:

Row      ID           Profile     Import ID     Field ID        Product
1         5            Null           5           60              Can
2         0            Null           5           65              Hat
3         0            Null           5           70              Box
4         6            Null           6           60              Fish

I basically want to take the word "Hat" in row 2 and place it into the "Profile" column of row 1, replacing the null value there. I am doing this for multiple rows.

我基本上想在第2行中使用“Hat”一词,并将其放入第1行的“Profile”列中,替换那里的null值。我正在为多行做这个。

In the case of the multiple rows I want to take the "Profile" column and make it equal to the "Product" column. I only want this to happen in the rows where the "ID" value matches the "Import ID", and where the "Field ID" is 65 specifically. In the example above the "ID" 5 matches the "Import ID" 5, so I want to take the "Product" value "Hat" where the "Field ID" is 65, and place that value into the "Profile" column where the ID is 5. My table has over 9000 rows and 600 would have to be changed in this way, with various ID's needing various products inserted.

在多行的情况下,我想取“Profile”列并使其等于“Product”列。我只希望在“ID”值与“导入ID”匹配的行中发生这种情况,并且“字段ID”具体为65。在上面的示例中,“ID”5与“导入ID”5匹配,因此我想将“字段ID”为65的“Product”值设置为“Hat”,并将该值放入“Profile”列中ID为5.我的表有超过9000行,必须以这种方式更改600,各种ID需要插入各种产品。

The result I would like would be:

我想要的结果是:

Row      ID           Profile     Import ID     Field ID        Product
1         5            Hat           5           60              Can
2         0            Null          5           65              Hat
3         0            Null          5           70              Box
4         6            Null          6           60              Fish

I pray that makes sense...

我祈祷这是有道理的......

My query was this

我的疑问是这个

UPDATE 'Data' SET 'Profile'='Product' WHERE 'ID'='Import ID' AND 'Field ID'=65;

更新'数据'SET'配置文件'='产品'WHERE'ID'='导入ID'和'字段ID'= 65;

I have also tried a subquery

我也试过了一个子查询

UPDATE 'Data' SET 'Profile'= (SELECT 'Product' FROM Data WHERE 'Field ID'=65) WHERE 'ID'='Import ID';

更新'数据'SET'配置文件'=(选择'产品'FROM数据WHERE'字段ID'= 65)WHERE'ID'='导入ID';

This did not work and I am just wondering if there is some logic I missing. Thank you to anyone who can help, I have been up for a bit trying to understand this...

这不起作用,我只是想知道是否有一些错过的逻辑。谢谢任何可以提供帮助的人,我已经有点想要了解这一点......

2 个解决方案

#1

You need to join the data; something like:

你需要加入数据;就像是:

UPDATE d1
SET d1.Profile = d2.Product
FROM [Data] d1 -- dest
INNER JOIN [DATA] d2 -- source
    ON d2.[Import ID] = d1.[ID] AND d2.[Field ID] = 65

(note swapped 2 columns...)

(注意交换了2列...)

#2

A couple thing to keep in mind when learning sql:

学习sql时要记住几件事:

it isnt a good idea to have spaces in column names. although they might be easier to read, it makes your queries more difficult. most databases dont allow them at all, and those that do have different ways to specify the columns in queries.

在列名中包含空格不是一个好主意。虽然它们可能更容易阅读,但它会使您的查询更加困难。大多数数据库根本不允许它们,而那些确实有不同方法来指定查询中的列。

to work around your problem, perhaps you should try to enclose the column name in backticks (`), or in square brackets ([ ]).

要解决您的问题,也许您应该尝试将列名括在反引号(`)或方括号([])中。

in any case, instead of a space, please consider an underscore.

在任何情况下,请考虑下划线而不是空格。
with that in mind you should also remember that not to put column names in quotes. something like

考虑到这一点,你还应该记住,不要将列名放在引号中。就像是

SELECT 'Product' FROM Data WHERE 'Field ID'=65

选择'产品'FROM数据WHERE'字段ID'= 65

would not work for two reasons:

不会有两个原因:

a. Selecting quoted text will return that quoted text. so were the where clause to return two rows, you would get the text 'Product' returned twice.

一个。选择带引号的文本将返回引用的文本。那么返回两行的where子句,你会得到文本'Product'返回两次。

b. here your where clause is comparing the text 'Field ID' with the number 65, which would always be false.

湾这里你的where子句将文本'Field ID'与数字65进行比较,这总是假的。

hope that helps

希望有所帮助

#1