Embedded SQL Update Query

2505 pts.
Tags:
Embedded SQL
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

Answer Wiki

Thanks. We'll let you know when a new response is added.

Try this:
————–
update FileA
set telefon = (select telefon from FileB where FileA.empid = FileB.empid)
where empid in(<here is comma-list of ids of your 3-4 employee>)
—————-

Discuss This Question: 7  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • TomLiotta
    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:
    report
  • RamvishakRamesh
    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:
    report
  • TomLiotta
    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:
    report
  • msi77
    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:
    report
  • TomLiotta
    ...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:
    report
  • msi77
    ...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:
    report
  • Sureyz
    Its working fine
    2,430 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following