为表复制/更新 MYSQL 构造查询

发布于 2024-11-25 09:34:27 字数 763 浏览 1 评论 0原文

目前正在寻找从两个表中提取数据并将其插入到另一个表中。表格如下（包含理想数据示例）：

Table1
T1ID(PK)  T1Field1
301       2011-07-01
302       2011-07-01

Table2
T2ID(PK)  T2Field2  T2Field3  T2Field4
521       301       555       Apple
522       301       666       Pear


Table3 (new table)
T3Field1  T3Field2     T3Field3    T3Field4
301       2011-07-01   555         Apple
301       2011-07-01   666         Pear

寻找最少且最有效的查询来执行以下操作：

将 T1ID 插入 T3Field1 + 将 T1Field1 插入 T3Field2
更新 T3Field3，其中 T3Field1 = T2Field2（对于每个）-*

第一个查询仅使用 mysql 插入。这需要首先完成，因为它包含时间戳信息。

*在这里尝试 INNER JOIN 但没有成功，因为存在一对多并且只有第一行会被插入。

我可以选择所有结果，然后在每个记录中循环/更新，但我试图将其尽可能保持最小。

只是需要对逻辑的第二意见！

原文

Currently looking to extract data from 2 tables and insert into another.
Tables as follows (with example ideal data):

Table1
T1ID(PK)  T1Field1
301       2011-07-01
302       2011-07-01

Table2
T2ID(PK)  T2Field2  T2Field3  T2Field4
521       301       555       Apple
522       301       666       Pear


Table3 (new table)
T3Field1  T3Field2     T3Field3    T3Field4
301       2011-07-01   555         Apple
301       2011-07-01   666         Pear

Looking for the fewest and most efficient query's to use in order to do the following:

Insert T1ID into T3Field1 + Insert T1Field1 into T3Field2
Update T3Field3 where T3Field1 = T2Field2 (for each) -*

Have simply used mysql insert for the first query. This NEEDS to be done first as it contains timestamp information.

*Attempted INNER JOIN here with no luck since there is a one-to-many and only the first row would get inserted.

I could select all of the results and then while loop / update within for each record but am trying to keep this as minimal as possible.

Just need a second opinion on the logic!

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

你的呼吸 2024-12-02 09:34:27

我测试了下面的代码，它符合您的理想数据。但是，Table1 的第二行丢失了，因为 Table2 中没有相应的匹配项

INSERT INTO Table3
SELECT 
Table1.T1ID AS T3Field1,
Table1.T1Field1 AS T3Field2,
Table2.T2Field3 AS T3Field3,
Table2.T2Field4 AS T3Field4
FROM Table1, Table2
WHERE
Table1.T1ID = Table2.T2field2

I tested the below code and it matches your ideal data. But, second row of Table1 is lost since there is no corresponding match for it in Table2

INSERT INTO Table3
SELECT 
Table1.T1ID AS T3Field1,
Table1.T1Field1 AS T3Field2,
Table2.T2Field3 AS T3Field3,
Table2.T2Field4 AS T3Field4
FROM Table1, Table2
WHERE
Table1.T1ID = Table2.T2field2

回复收藏 0 原文

~没有更多了~