Suppose I have a file with empid and telephone number, I realised the telephone number value is not correct for some 3-4 employee ids. I have a backup file for this file and it has the correct telephone number value . I need to write an SQL querry to correct the wrong telephone number by making use of the backup file. Say new file is FileA and Backup file is FileB
The 'Answer' above is correct enough. But the question is not clear.
Why was "Embedded SQL..." part of the subject? Is there a problem related to embedded SQL? Neither the query nor the problem seems related to embedded SQL. Is there more to the problem?
Tom
Msi77,
Thanks for the response, but in the situation I mentioned, I dont know what all are the employee id's whose telephone numbers are not correct. Just for simplicity I said 3-4. I only knows there are incorrect telephone numbers. In that case, how can I implement this?
Tom, I mentioned Embedded SQL because, I want to write the query inside an RPG program.
In that case, how can I implement this?
There are two general methods.
First, you can create a dynamic SQL statement. You need to input the telephone numbers somehow into your program. Maybe a user types them in one at a time or types them into a list (maybe like a subfile). When your program has all of the numbers, you concatenate them into a string that includes the SQL like Msi77 put in the 'Answer'. Use SQL PREPARE and EXECUTE to execute the SQL after you have the whole statement.
But better is to have a file to enter your list of employee numbers and new phone numbers into a new table. Then run this SQL:
update FileA
set telefon = (select telefon from FileB where FileA.empid = FileB.empid)
where empid in(select empid from FileB)
The first SELECT tells the UPDATE what the new phone numbers are. The second SELECT tells which rows need to be updated. It's almost the same as in the 'Answer', but everything is outside of your program. The SQL can be compiled and used again later.
You can put new rows into FileB and run the program again. The coding will be much simpler.
Tom
In Tom's solution all records will be updated correct telephone number or not.
I'd rewrite this:
update FileA
set telefon = (select telefon from FileB where FileA.empid = FileB.empid)
where empid in(select b.empid from FileB b join FileA a
on b.empid = a.empid and a.telefon <> b.telefon)
...all records will be updated correct telephone number or not.
Well, not "all records". Only records where FileB has a matching empid.
It's true, though, that a telephone number in FileA for a matching empid might be updated with the same value that is already in that row. But if an empid shouldn't be updated, don't put the row in FileB.
For this function, FileB is just a transaction file. After the updates are applied, the "transactions" can (should) be cleared. The deletions would remove all rows from FileB that match empid and telefon from FileA.
There can be a validation applied to inserts into FileB so that no transactions are allowed into FileB if the telefon value already matches against File A for that empid.
However, if there is a concern that the update might set a telephone number to the same value that it already has, then the addition of "a.telefon <> b.telefon" as a test could be useful. When the volume is very high, that could reduce a little I/O.
Tom
...Well, not “all records”. Only records where FileB has a matching empid.
Yes, but as I supposed FileB is backup file, so it contains all the rows from FileA except those which was inserted/updated after this backup was made.
Sergey
Free Guide: Managing storage for virtual environments
Complete a brief survey to get a complimentary 70-page whitepaper featuring the best methods and solutions for your virtual environment, as well as hypervisor-specific management advice from TechTarget experts. Don’t miss out on this exclusive content!
Discuss This Question: 7  Replies