Solved

Update field incrementally

Posted on 2014-02-03
16
392 Views
Last Modified: 2014-02-04
Here is the update query I need to run

UPDATE PRODUCT_CODE SET BVRVADDTIME = 12543222;

Open in new window


But I want to update the field incrementally. It is a string field. How can I accomplish this?

It is on a Pervasive SQL database
0
Comment
Question by:Gerhardpet
  • 6
  • 6
  • 4
16 Comments
 
LVL 45

Expert Comment

by:Kdo
ID: 39829572
By incrementally, do you mean that you want to value to increment every time a row is changed?
0
 
LVL 1

Author Comment

by:Gerhardpet
ID: 39829578
Yes that is correct.
0
 
LVL 45

Expert Comment

by:Kdo
ID: 39829607
I have no experience with the Pervasive product, so I'm relying on techniques that SQL has used on other platforms.

Something like this may work:


UPDATE PRODUCT_CODE
  SET BVRVADDTIME = 0;

UPDATE PRODUCT_CODE
  SET BVRVADDTIME = 12543222 + (select count(*) from product_code where bvrvaddtime >= 12543222);
0
 
LVL 28

Expert Comment

by:Bill Bach
ID: 39829663
Pervasive PSQL is like just about every other SQL database out there -- it accepts "standard" SQL syntax.  Of course, each SQL engine has its own "standard", but mostly they support the same functions.

The first problem with your query is that it will update EVERY record in the specified table.  This seems to be a bit wrong, if you ask me, as there is usually some sort of WHERE clause to limit the number of records being updated.  Further, there is usually some sort of unique field (a primary key) that can be helpful here.

This query should do it, though:
   UPDATE PRODUCT_CODE SET BVRVADDTIME = CONVERT(CONVERT(BVRVADDTIME,SQL_INTEGER) +1,SQL_CHAR);

Note that we first need to convert the string field to a INTEGER field (max value is 2B, so use UINTEGER if you need 4B or UBIGINT for even larger capacities), add one, then convert it back to a string field.  (Again, use SQL_VARCHAR if the data type requires it.)  For more details on this function, go into the Documentation (PCC/Help/Docs) and open up Advanced Reference, SQL Engine Reference, Scalar Functions, Conversion Functions).
0
 
LVL 1

Author Comment

by:Gerhardpet
ID: 39829682
In this case the table has only 64 records and I want to update all records.

I will try your suggestion and report back.
0
 
LVL 45

Expert Comment

by:Kdo
ID: 39829737
CONVERT is a SQL SERVER function and is not part of ANSI SQL.  I doubt that it will work in Pervasive SQL.
0
 
LVL 28

Expert Comment

by:Bill Bach
ID: 39829779
And WHY would you doubt that?  Originally, the CAST() function was provided within the PSQL engine (going back to 1997, or perhaps even earlier).  The CONVERT() function was added in the Pervasive PSQL v9.5 release in 2006, when Pervasive decided to try to appease the Microsoft SQL Server developers by including many of the functions that Microsoft-centric developers were used to using.
0
 
LVL 1

Author Comment

by:Gerhardpet
ID: 39829890
@ Kdo
Your query does not work


@BillBach
Your query works but it does not increment the records. After running your query it just make all records = 1

What I want is starting with this value 13005421 (or any 8 digit value) and then for all records increment. Crystal sees this field as a date/time but using DDF in Pervasive Control Center it is a string field.

Here is what I need for each record

13005421
13005422
13005423
13005424
13005425
etc....
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 28

Expert Comment

by:Bill Bach
ID: 39829978
Interesting.  Perhaps your data is not as clean as you expect?  I tested this with PSQL v11.31 (the current release) using this simple script:

CREATE TABLE "PRODUCT_CODE"(
 "BVRVADDTIME" CHAR(50));
INSERT INTO PRODUCT_CODE VALUES ('13005421');
INSERT INTO PRODUCT_CODE VALUES ('13005421');
INSERT INTO PRODUCT_CODE VALUES ('13005421');
select * from "PRODUCT_CODE";
UPDATE PRODUCT_CODE
SET BVRVADDTIME = CONVERT(CONVERT(BVRVADDTIME,SQL_INTEGER) +1,SQL_CHAR);
select * from "PRODUCT_CODE";

When I run this script, I end up with this:
BVRVADDTIME                                      
==================================================
13005422                                          
13005422                                          
13005422                                          
3 rows were affected.

So, this query works in the current version, with the CHAR data type, and with clean data.  Can you confirm that this same script works on your system?  This will verify that the your version of the engine is working properly, at least.  If it fails, please  post your results and exact PSQL version.  If it works, please post your data type and the EXACT layout of the data inside that field.  There may be leading or trailing characters to contend with.

Also, your original inquiry wanted to change all records to the same value, but your last message is unclear -- should they all have the SAME value, or should each record be incrementing by one over the previous record?
0
 
LVL 1

Author Comment

by:Gerhardpet
ID: 39830455
I ran the script and got the same results

BVRVADDTIME                                      
==================================================
13005422                                          
13005422                                          
13005422                                          

3 rows were affected.

I did this in a new database

I'm on version 10. When I run your query it just updates all fields by 1 increment but I need to update them sequentially.

Sorry my mistake...I should have said sequentially increment. This field is not indexed but that is what use it for to sync data to MS SQL using SSIS. Each record need to be unique.

So I want to start at 13005421 and then  sequentially increment by 1
13005422
13005423
13005424
13005425
etc....
0
 
LVL 28

Expert Comment

by:Bill Bach
ID: 39830693
OK -- now it makes a bit more sense.  The SQL query suggested by Kdo fails because the SELECT inside the WHERE clause is evaluated FIRST, and the result is also not going to be an incrementing value.  

There are two possible solutions:
1) If the data is independent from the application, then changing the field from a CHAR field to an IDENTITY would make the most sense.  IDENTITY fields in PSQL are also called AUTOINC fields, and they are guaranteed a unique value, and if a new record is added with a 0 value, it gets the next highest value by default.  This would be the easiest solution if you can change the data type.

2) If you cannot change the data type (i.e. this is some sort of note field in an existing application), then you'll need to write a stored procedure.  Let me build a sample SP for you...
0
 
LVL 1

Author Comment

by:Gerhardpet
ID: 39830706
Unfortunately I'm not able to change the data type because the data is dependent on the Sage ERP application

Thanks for your help! I'll wait for your sample SP
0
 
LVL 28

Expert Comment

by:Bill Bach
ID: 39830735
It may be a day or so.  I wrote a sample script that SHOULD work, and it crashed both my PSQLv11 and my older PSQLv10 database engines....
0
 
LVL 1

Author Comment

by:Gerhardpet
ID: 39830766
No problem! I'll wait for it...
0
 
LVL 28

Accepted Solution

by:
Bill Bach earned 500 total points
ID: 39830767
OK.  Found a typo that caused the crash in both engines -- Actian can figure out how to fix that one.  In the meantime, here's a usable script:

Create Procedure SeedProductCode(IN :Start UINTEGER) WITH DEFAULT HANDLER AS 
BEGIN 
   DECLARE :tmp CHAR(50);
   DECLARE curs CURSOR FOR SELECT BVRVADDTIME FROM PRODUCT_CODE FOR UPDATE;
   OPEN curs;
   FETCH NEXT FROM curs INTO :tmp;
   WHILE(SQLSTATE = '00000') DO
     UPDATE SET BVRVADDTIME = CONVERT(:Start,SQL_CHAR) WHERE CURRENT OF curs;
     SET :Start  = :Start  + 1;
     FETCH NEXT FROM curs INTO :tmp;
   END WHILE;
   CLOSE curs;
END;
Call SeedProductCode(13005421);

Open in new window


Note that you'll have to create the procedure ONE time in the database.  After that, calling the the SP with the starting seed value you want is trivial.  While the system SHOULD handle data conversions properly, your BEST solution will declare the :TMP variable as a data type that exactly matches your source field type.
0
 
LVL 45

Expert Comment

by:Kdo
ID: 39830812
Hi guys....

Bill -- I had no idea that Pervasive had moved to more closely align with SQL Server.  Most vendors seem to like their own autonomy and fight hard to maintain their distinctiveness.


Regarding my original code, I was trying to duplicate the method used before ROW_NUMBER and the other analytic functions became popular.  SQL Server 2000, Oracle 8, DB2 V6, etc would have all used the older method.  Basically, the query would enumerate all of the desired rows using count(*).  It requires a unique value or key to work as intended.  The primary key (product_code) would work well.

SELECT 
  product_code, 
  (SELECT count (*) FROM product_table a where a.product_code < b.product_code) as RN
FROM product_table b;

Open in new window



I won't pretend to know how to format the UPDATE statement.  The syntax for an update of joined tables is different on all major DBMS.  And the use of a CTE seems unlikely, too.

That said, this should work (with some tuning to conform to the Pervasive SQL syntax)
UPDATE product_table c,
(
  SELECT 
    product_code, 
    (SELECT count (*) FROM product_table a where a.product_code < b.product_code) as RN
  FROM product_table b
) d
SET c.brvraddtime = 12543222 + d.RN
WHERE c.product_code = d.product_code;

Open in new window




Good Luck,
Kent
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now