[firebird-support] Query to update multiple records in same Table
Hi Using Firebird 2.5 Is there a single query that allows multiple row updates from multiple row selects (e.g. like Copy paste) i.e. Select row 1 column 7 update row 101 column 7 Select row 2 column 7 update row 102 column 7 Etc for say 20 rows Column 1 (row) is ‘TAG_NO’ pk ascending, Column 7 Is’ NAME’ At the moment I have used Execute block that works ok but wonder if possible using a query execute block -- Copy 19 words from MMH UOP to another UOP section as declare variable i integer; begin i = 2433; -- start Tag No of MMH UOP while (i 2452) -- 19 words to be copied that all UOP's have. Phase No, Brand etc. do begin update analog a1 set a1.name = (select a2.name from analog a2 where a2.tag_no = :i) where a1.tag_no = :i + 1238; -- 1238 is Offset from 2433 to start address of CIP UOP = 2433 +1238 i = i+1; end end; By accident I came up with a query that appears to work but have not finished fully testing understanding, so would like the help of this skilled group for a known solution. Thanks for any help Jack
[firebird-support] Advice requested on design pattern
I am creating a database that consists of 30 - 40 tables. Each table stores a specific set of data which has a specific set of attributes. There are also attributes common to all tables for example: * UID (from generator) * Name * Description * DateTime Created (audit field) * DateTime Modified (audit field) * User Created (audit field) * User Modified (audit field) Most tables will contain a few thousands of records, some of them may be largish blobs such as photos but mostly it will be plain text and HTML. Normally insertions and updates would be infrequent but retrieval needs to be as fast as possible. The data is being displayed in a relatively simple client written in C++ and using IBPP. Q1: I understand that Firebird does not support table inheritance. Therefore is it better to create a 'COMMON_FIELDS' table and then join the 'specialised' tables to it or include the common fields (i.e. the list above) in each and every table? The 'created' and 'modified' audit fields should be updated automatically and obviously I will use a trigger to do this. One advantage I can see of using a single COMMON_FIELDS table is that I can write one stored procedure and call it from the 'before' trigger of each table. Alternatively in the case where the common fields are repeated in each table I could pass the table name as a parameter to the SP. Q2: Are there any pitfalls (re-entrance) in calling a common stored procedure with a table name from a trigger and have the SP take care of the audit field updates (not exactly sure how to do this yet)? It would be good if the audit fields were not easy to fiddle with (i.e. by someone using FlameRobin or isql). The obvious way would be for them to be 'lightly' encrypted (i.e. obfuscated) during write in the SP and decrypted in the application. Q3: Is it possible to hide the SP from a casual observer or would it be better to write a UDF for the obfuscation? I appreciate that the answer may be Depends ... but I would appreciate general guidance or opinions where it isn't possible to provide a definite answer. Many thanks for the help!!
Re: [firebird-support] Query to update multiple records in same Table
On 2015-05-19 o 13:59, millsjac...@yahoo.com [firebird-support] wrote: Is there a single query that allows multiple row updates from multiple row selects (e.g. like Copy paste) i.e. Select row 1 column 7 update row 101 column 7 Select row 2 column 7 update row 102 column 7 Etc for say 20 rows Column 1 (row) is ‘TAG_NO’ pk ascending, Column 7 Is’ NAME’ At the moment I have used Execute block that works ok but wonder if possible using a query execute block -- Copy 19 words from MMH UOP to another UOP section as declare variable i integer; begin i = 2433; -- start Tag No of MMH UOP while (i 2452) -- 19 words to be copied that all UOP's have. Phase No, Brand etc. do begin update analog a1 set a1.name = (select a2.name from analog a2 where a2.tag_no = :i) where a1.tag_no = :i + 1238; -- 1238 is Offset from 2433 to start address of CIP UOP = 2433 +1238 i = i+1; end end; By accident I came up with a query that appears to work but have not finished fully testing understanding, so would like the help of this skilled group for a known solution. Usually the simplest solutions work best, so I would go for: update ANALOG a set a.NAME=(select NAME from ANALOG where TAG_NO=a.TAG_NO-1238) where a.TAG_NO = 3671; (3671 = 2433 + 1238) Eventually you can further filter the rows being updated by adding more terms to the where clause. regards Tomasz Thanks for any help Jack -- __--==--__ __--== Tomasz Tyrakowski==--__ __--==SOL-SYSTEM==--__ __--== http://www.sol-system.pl ==--__ __--==--__
