使用嵌入的select语句创建Update查询 [英] Creating an Update query with an embedded select statement

问题描述

我已经编码了很多这样的方法我现在感到困惑：这是我想做的但我无法使代码工作：

表：

Tab1：

group_key

date

金额

agy

fy


Tab2：

group_key

描述

地址

fy - 空白

agy - 空白


我希望tab1中的fy和agy填充在两个表中Group_key匹配的选项卡2中。下面是我尝试的代码和我收到的错误我希望你能帮忙。


首先尝试：

I have coded this so many ways I am now confused: here is what I would like to do but I cannot get the code to work:
Tables:
Tab1:
group_key
date
amount
agy
fy

Tab2:
group_key
description
address
fy - blank
agy - blank

I want the fy and agy from tab1 to be populated in tab 2 matching on Group_key from both tables. Below is the code I have tried and the errors I am receiving I hope you can help.

first try:

展开 | 选择 | Wrap | 行号

推荐答案

您正在复制不需要复制的信息。当您需要该信息时，您可以加入表格来检索它，不需要存储两次。那会打破规范化的规则。请阅读我们关于规范化的文章： http://bytes.com /topic/access/insigh...ble-structures 。


至于你的尝试......


在你的第一次尝试时，您在该位置的子查询必须只返回一条记录。您收到错误，因为您的子查询返回的是一条记录。


在第二次尝试中，您试图在WHERE子句中为子查询设置别名。别名用于FROM子句中的记录集或SELECT子句中的列。它们不在WHERE子句中使用。除此之外，您的子查询本身在WHERE子句中没有做任何事情。


在第三次尝试中，DB2不允许在UPDATE语句中使用FROM子句。

You are duplicating information that does not need to be duplicated. When you need that information, you can just join to the table to retrieve it, there''s no need to store it twice. That would break the rules of normalization. Please read our article on normalization here: http://bytes.com/topic/access/insigh...ble-structures.

As for your attempts...

In your first attempt, your subquery in that position must only return one record. You are getting an error because your subquery is returning more that one record.

In your second attempt, you are trying to alias a subquery in a WHERE clause. Aliases are for record sets in the FROM clause or columns in the SELECT clause. They are not used in the WHERE clause. Aside from that, your subquery by itself in the WHERE clause doesn''t do anything.

In your third attempt, DB2 does not allow a FROM clause in the UPDATE statement.

有一个原因我尝试这个，它不是为了查找目的，你可以帮助我使查询工作吗？我们的ADHOC系统中有大量重复数据。我知道为什么我的数据是这样的，我在查询上寻求帮助而不是在寻找对我的数据布局的批评我没有创建这个怪物我试图解决空间问题而不必重新编写系统。我希望你可以帮助纠正我的查询而不只是告诉我有什么问题，DB2正在这样做。顺便说一句，感谢我给出了我的查询错误的DB2解释，我知道出了什么问题我只是试图更好地理解我想要做的事情。

There is a reason I am attempting this, it''s not for look up purposes, can you help me with making the query work? We have a lot of duplicated data in our ADHOC system. I know why my data is the way it is, I am asking for help on a query not looking for criticisms on my data layout I did not create this monster I am trying to solve a space issue with out having to re write a system. I hope you can help with correcting my query and not just tell me what is wrong, DB2 is doing doing that. By the way, thank for giving the DB2 explanations of my query errors, I knew what was wrong I was just showing the attempts for a better understanding of what I was trying to do.

你的第一次尝试是最接近的。由于子查询应该只返回一行，因此修复将使它返回您需要的一行。这不是我可以为你回答的，因为你没有告诉我们你需要什么行。你只描述了tab1和tab2之间的关系，你还没告诉我们table2是如何与这一切相关的。

Your first attempt was the closest. Since the subquery should only return one row, the fix then is to have it return the one row that you need. That''s not something I can answer for you because you haven''t told us what row you need. You only describe the relationship between tab1 and tab2, you haven''t told us how table2 is related to all this.

这篇关于使用嵌入的select语句创建Update查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！