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.
Table - LName
Feild Name
Table - Descripion
Field Name
Field Add
Field Job
Now, table LName has just the one field and it is populated.
Table Description has data in all fields except the 'Name' field.
I need to put the data from Field 'Name' in table LName into Field 'Name' in table Description.
Either that or merge both tables into one table (File3) that has all the fields but no data.
Appreciate any help.
Barry
Update 2:
I was able to craete a logical file that does what I want but only returns 1 record.
The 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') ;
Output of Query
System Name Alternate Pager Name
CHEVY AS400 PRIM
So now I have to figure a way for this SQL statement to read through all 28 records.
Barry
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.)
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
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)
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.
Without an index in common this makes this task seemingly impossible... So this example only works by implementing a index of id on each table and setting them in order of the the assigned indexes. Sorry if this is confusing...
UPDATE LNAME DESCRIPTION
INNER JOIN DESCRIPTION ON
DESCRIPTION.id = LNAME.id
SET DESCRIPTION.field_name = LNAME.field_name;
You can create a new table:
CREATE TABLE new_table AS (SELECT ln.Name, d.Add, d.Job FROM LName ln, Description d);
BUT I don't know why you want to do this... I mean, you need a relation between LName table and Description table, like a common id or something...
What exactly do you want to do?
INSERT INTO Description (FieldName)
SELECT FieldName FROM LName
is what you're looking for. This will put all of the FieldNames from the LName table into the Description table - you can then update the other fields.
Unless you're trying JOIN the two tables where Decription.FieldName is null and LName.FieldName isn't... which is just not possible.
