Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 410
  • Last Modified:

Update field incrementally

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
Gerhardpet
Asked:
Gerhardpet
  • 6
  • 6
  • 4
1 Solution
 
Kent OlsenData Warehouse Architect / DBACommented:
By incrementally, do you mean that you want to value to increment every time a row is changed?
0
 
GerhardpetAuthor Commented:
Yes that is correct.
0
 
Kent OlsenData Warehouse Architect / DBACommented:
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
A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

 
Bill BachPresidentCommented:
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
 
GerhardpetAuthor Commented:
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
 
Kent OlsenData Warehouse Architect / DBACommented:
CONVERT is a SQL SERVER function and is not part of ANSI SQL.  I doubt that it will work in Pervasive SQL.
0
 
Bill BachPresidentCommented:
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
 
GerhardpetAuthor Commented:
@ 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
 
Bill BachPresidentCommented:
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
 
GerhardpetAuthor Commented:
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
 
Bill BachPresidentCommented:
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
 
GerhardpetAuthor Commented:
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
 
Bill BachPresidentCommented:
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
 
GerhardpetAuthor Commented:
No problem! I'll wait for it...
0
 
Bill BachPresidentCommented:
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
 
Kent OlsenData Warehouse Architect / DBACommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

  • 6
  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now