TomLiotta Jun 14, 2012   0:38 AM GMT

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

125,585 pointsBadges:

RamvishakRamesh Jun 14, 2012   4:33 AM GMT

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.

2,505 pointsBadges:

TomLiotta Jun 14, 2012   6:34 AM GMT

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

125,585 pointsBadges:

msi77 Jun 14, 2012   5:17 PM GMT

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)

1,670 pointsBadges:

TomLiotta Jun 14, 2012   10:36 PM GMT

...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

125,585 pointsBadges:

msi77 Jun 15, 2012   1:56 PM GMT

...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

1,670 pointsBadges:

Sureyz Aug 20, 2012   6:22 AM GMT

Its working fine

2,825 pointsBadges: