I am very new to this.  This is what I want to do....

Import text file with data into a table.  There is data in the table already, but I need to it update with this new data.  There are only 3 fields to
change.  This will be done many times a day.  (Shipping data from UPS into my Point of Sale)

I am unsure how to write the SQL query to do this.  Any suggestions?

Andy


As an overview you'll have to import the data into a temporary table and then join that with your table to do the update. If you have a look in the manual (I use the pdf version) you'll see a sample of updating a table with data from another table.

Roy Lambert

Andy,

<< I am very new to this.  This is what I want to do....

Import text file with data into a table.  There is data in the table
already, but I need to it update with this new data.  There are only 3
fields to change.  This will be done many times a day.  (Shipping data from
UPS into my Point of Sale)

I am unsure how to write the SQL query to do this.  Any suggestions? >>

You'll need 3 separate SQL statements in a script (be sure to end each
statement with a semicolon (:

1) CREATE TABLE to create a temporary or in-memory table for use in the
import.
2) IMPORT TABLE to import the data into the temporary or in-memory table.
3) UPDATE to update the data in the existing table by joining it to the
temporary or in-memory table.

See here:

http://www.elevatesoft.com/dbisam4d7_create_table_statement.htm
http://www.elevatesoft.com/dbisam4d7_import_table_statement.htm
http://www.elevatesoft.com/dbisam4d7_update_statement.htm

--
Tim Young
Elevate Software
www.elevatesoft.com

"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote:

Andy,

<< I am very new to this.  This is what I want to do....

Import text file with data into a table.  There is data in the table
already, but I need to it update with this new data.  There are only 3
fields to change.  This will be done many times a day.  (Shipping data from
UPS into my Point of Sale)

I am unsure how to write the SQL query to do this.  Any suggestions? >>

You'll need 3 separate SQL statements in a script (be sure to end each
statement with a semicolon (:

1) CREATE TABLE to create a temporary or in-memory table for use in the
import.
2) IMPORT TABLE to import the data into the temporary or in-memory table.
3) UPDATE to update the data in the existing table by joining it to the
temporary or in-memory table.

See here:

http://www.elevatesoft.com/dbisam4d7_create_table_statement.htm
http://www.elevatesoft.com/dbisam4d7_import_table_statement.htm
http://www.elevatesoft.com/dbisam4d7_update_statement.htm

--
Tim Young
Elevate Software
www.elevatesoft.com


Thanks for the help.  Here is my statement, but I am getting errors with the join.  Is there something I am not doing right?

CREATE TABLE IF NOT EXISTS table_temp

(

Number CHAR(50) DESCRIPTION 'Number',
Shipping CHAR(50) DESCRIPTION 'Shipping' DEFAULT 0.00,
TrackNum CHAR(50) DESCRIPTION 'Tracking Number',
PRIMARY KEY (Number)

);

IMPORT TABLE table_temp

FROM "C:\UPS_Export.csv"
WITH HEADERS
COLUMNS (Number,TrackNum,Shipping);

UPDATE invoice [EXCLUSIVE] SET Number=table_temp.Number
FROM invoice JOIN table_temp
ON table_temp.number=invoice.number
;
UPDATE invoice [EXCLUSIVE] SET Shipping=table_temp.Shipping
FROM invoice JOIN table_temp
ON table_temp.number=invoice.number
;
UPDATE invoice [EXCLUSIVE] SET TrackNum=table_temp.TrackNum
FROM invoice JOIN table_temp
ON table_temp.number=invoice.number

UPDATE invoice [EXCLUSIVE] SET Number=table_temp.Number
FROM invoice JOIN table_temp
ON table_temp.number=invoice.number
;
UPDATE invoice [EXCLUSIVE] SET Shipping=table_temp.Shipping
FROM invoice JOIN table_temp
ON table_temp.number=invoice.number
;
UPDATE invoice [EXCLUSIVE] SET TrackNum=table_temp.TrackNum
FROM invoice JOIN table_temp
ON table_temp.number=invoice.number

Well I messed up the ON statements which I did fix to show the right fields.  But the JOIN error still shows up.  What am I doing wrong?

Andy,

Two things:

1) You can just do the whole thing in one statement.
2) You don't need the EXCLUSIVE part, and it normally would be specified
without the brackets anyway, which are only there in the docs to indicate
that the keyword is optional.

UPDATE invoice
SET Number=table_temp.Number, Shipping=table_temp.Shipping,
TrackNum=table_temp.TrackNum
FROM invoice JOIN table_temp
ON table_temp.number=invoice.number

BTW, nice job for a first crack at something which I gave very brief details
about.

--
Tim Young
Elevate Software
www.elevatesoft.com

Thanks Tim for the vote of confidence.  I am getting an error now that I'm not sure what to do about.  DBISAM Engine Error#11949 SQL Parsing
Error - Expected NULL, Boolean, SmallInt, Word...etc., but instead found table_temp.Number in UPDATE SQL statement at line 21, column 12

Here is what my code looks like now...

DROP TABLE IF EXISTS table_temp;

CREATE TABLE IF NOT EXISTS table_temp

(

Number CHAR(10) DESCRIPTION 'Number',
Shipping CHAR(10) DESCRIPTION 'Shipping' DEFAULT 0.00,
TrackNum VARCHAR(50) DESCRIPTION 'Tracking Number',
PRIMARY KEY (Number)

);

IMPORT TABLE table_temp

FROM "C:\UPS_Export.csv"
WITH HEADERS
COLUMNS (Number,TrackNum,Shipping);

UPDATE invoice
SET Number=table_temp.Number, Shipping=table_temp.Shipping,
TrackNum=table_temp.TrackNum
FROM invoice JOIN table_temp
ON table_temp.number=invoice.number

Do I need a SELECT statement anywhere in here?  I'm just throwing stuff out here, not sure what to do

Thanks for all the help, but being a newbie to all this, I actually fixed the problem.  Turns out to be a datatype error, the created table and table I was updating had the
wrong type of fields in them.  Shipping Field should have been FLOAT.  That fixed it right away.

Andy,

<< Thanks for all the help, but being a newbie to all this, I actually fixed
the problem.  Turns out to be a datatype error, the created table and table
I was updating had the wrong type of fields in them.  Shipping Field should
have been FLOAT.  That fixed it right away. >>

Good catch.  That's what my response was going to be.

--
Tim Young
Elevate Software
www.elevatesoft.com