SQL-合并两个表和一个公共字段 [英] SQL - Merge two tables with one common field

问题描述

我已经搜索了很多，但是找不到我想要的东西.在SQL方面，我并不完全无能为力，但这超出了我的范围.

I've searched and searched but can't quite find what I'm looking for. I'm not totally inept when it comes to SQL but this is beyond me.

所以我有两个带有一个公共字段的表.

So I have two table with one common field.

表-LName
姓氏

Table - LName
Feild Name

表格-描述
栏位名称
字段添加
现场作业

Table - Descripion
Field Name
Field Add
Field Job

现在，表LName只有一个字段，并且已填充.

Now, table LName has just the one field and it is populated.

表说明的所有字段(名称"字段除外)中都有数据.

Table Description has data in all fields except the 'Name' field.

我需要将表LName中的字段'Name'中的数据放入表Description中的字段'Name'中.

I need to put the data from Field 'Name' in table LName into Field 'Name' in table Description.

要么将这两个表合并到一个包含所有字段但没有数据的表(File3)中.

Either that or merge both tables into one table (File3) that has all the fields but no data.

感谢任何帮助.
巴里

Appreciate any help.
Barry

更新2: 我能够创建符合我想要的逻辑文件，但只返回1条记录.

Update 2: I was able to craete a logical file that does what I want but only returns 1 record.

SQL:

CREATE VIEW MISBXG.TEST_VIEW ( D COLUMN SYSNAM CHAR (8 ) NOT NULL  
 SYSNAM , LT                                                       
 DIADEV ,                                                          
 DITOPG )                                                          
 AS                                                                
 SELECT ALLISERIES.SYSNAM, CMPALTDEV.DIADEV, CMPALTDEV.DITOPG FROM 
ITTOOLS.ALLISERIES ALLISERIES INNER JOIN MISBXG.CMPALTDEV CMPALTDEV
ON ALLISERIES.SYSNAM = CMPALTDEV.SYSNAM WHERE CMPALTDEV.DIADEV <   
ALLISERIES.SYSNAM ;                                                

LABEL ON COLUMN MISBXG.TEST_VIEW                                   
(SYSNAM IS 'System Name' ,                                        
 DIADEV IS 'Alternate           Device' ,                          
 DITOPG IS 'Pager    Name') ; 

查询输出

System Name      Alternate       Pager Name
CHEVY                            AS400 PRIM

因此，现在我必须找出一种方法来使该SQL语句读取所有28条记录.

So now I have to figure a way for this SQL statement to read through all 28 records.

巴里

推荐答案

您从未真正回答过我在评论中寻找的内容，现在您已经编辑了问题，因此我的评论甚至没有任何意义. (就其价值而言，我对CPYF的建议可能不是一个好主意，但我对您的处境的理解仍然是，现在仍然非常缺乏.)

You never really addressed what I was looking for in my comment, and now you've edited the question so my comment doesn't even really make sense. (For what it's worth, my suggestion of CPYF was probably not a good idea, but my understanding of your situation was, and still is, quite lacking.)

根据您在先前的编辑中所说的话，我想知道CMPALTDEV中的所有记录是否实际上是彼此重复的(副本数足以匹配ALLISERIES中的记录数).如果是这样，那么最简单的事情可能就是省去任何尝试加入连接的过程，而只需通过蛮力插入CMPALTDEV中的值即可.例如，如果空白和'AS400 PRIM'实际上是您要与每个ALLISERIES.SYSNAM值关联的常数值，则制作一个CMPALTDEV的空副本(我将其称为CMPALTDEV2)并且很难-对常量值进行编码，如下所示:

Based on something you said in your earlier edit, I am wondering if all the records in CMPALTDEV are actually duplicates of each other (with enough copies to match the number of records in ALLISERIES). If so, then the simplest thing to do is probably dispense with any attempt to join, and just plug in the values from CMPALTDEV by brute force. For example, if blank and 'AS400 PRIM' are effectively the constant values you are trying to associate with each of the ALLISERIES.SYSNAM values, then make an empty copy of CMPALTDEV (I'll call it CMPALTDEV2) and just hard-code the constant values as follows:

INSERT INTO CMPALTDEV2
  SELECT SYSNAM, '', 'AS400 PRIM' FROM ALLISERIES

另一方面，如果CMPALTDEV中的值变化，并且您确实想要执行的操作是通过相对记录号"进行匹配，那么IBM确实会为您提供RRN函数以这样做:

On the other hand, if the values in CMPALTDEV vary, and what you really want to do is match by "relative record number", IBM does give you the RRN function to do so:

INSERT INTO CMPALTDEV2
  SELECT T1.SYSNAM, T2.DIADEV, T2.DITOPG
  FROM ALLISERIES T1                        
    JOIN CMPALTDEV T2 ON RRN(T1) = RRN(T2)

请注意:使用相对记录号是在SQL中执行任何操作的一种简单的方法.它充满了潜在的陷阱，并且确实违背了SQL的本意.大多数SQL实现(对于其他数据库)都没有与RRN类似的东西，并且最佳实践是即使可以在IBM i上也尽可能避免使用它.

Please note: Using the relative record number is a pretty hackish way to do anything in SQL. It's full of potential pitfalls, and really goes against what SQL is meant to be. Most implementations of SQL (for other databases) don't have anything analogous to RRN, and best practice is to avoid using it if you can help it, even on the IBM i.

这篇关于SQL-合并两个表和一个公共字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！