SQL for updating one type of record with fields from another type.

• Article Type: General
• Product: Aleph
• Product Version: 16.02

Description:
Can you use SQL to update one type of record with fields from another type of record? For example, the z30_order_number has a truncated value of "AP04-00000" while the z68_order_number has the correct, complete value of "AP04-000001234". I tried this: SQL> update z30 set z30_order_number = 'AP04-00000' || substr (z68_order_number,11,4) where z68_rec_key = substr (z30_rec_key,1,14) and z30_order_number like 'AP04-00000%'; but I get "invalid column".
Can you help?

Resolution:
Although Ex Libris does not support personal scripts, we can suggest the following:

SQL> update z30 set z30_order_number = 'AP04-00000' || (select substr (z68_order_number,11,4) from z68 where z68_rec_key = substr (z30_rec_key,1,14) ) where z30_order_number like 'AP04-00000%';

(In this particular case there was just one item and one order for each ADM -- with a z30_rec_key like this: 001234567000010; and a z68_rec_key like this: 00123456700001. So the above SQL worked. Not sure this can be applicable to other examples.
Note: You should back up the z30 table using p_file_03 before running such an update. You should not do the SQL vunless you are certain that you have correct values and know what you are doing.