General information
Category:
Databases,Tables, Views, Indexing and SQL syntax
Title:
Strategy for Inserting new records using SQL
I have two nearly identical database tables STOCK and STOCK_PREVIOUS that have the same number of items. I also have a STOCK_REMOTE table. I'm updating STOCK_PREVIOUS and STOCK_REMOTE if the "units" don't match in STOCK and STOCK_PREVIOUS. (Unfortunately STOCK does not have a datetime stamp and it's out of my control, otherwise I would simply check that for a "changed" record). This works fine and ia extemely fast using a simple set relation.
However, if a new record is inserted into STOCK, I'd like the that record to be inserted into STOCK_PREVIOUS through this program using a SQL statement.
What would the SQL code look like to accomplish this? Do I need to break the RELATION and rescan? Thanks in advance.
Here's the code so far:
OPEN DATABASE test
SELECT 1
USE stock
SET ORDER to number
SELECT 2
USE stock_previous ORDER 1
SELECT 1
SET RELATION TO number INTO stock_previous
SCAN
*If the units don't match
IF a.units<>b.units
*Update the remote_stock
UPDATE stock_remote ;
SET units=stock.units ;
where stock.number=stock_remote.number ;
AND stock.units<>stock_remote.units
*Update the local stock_previous
UPDATE stock_previous ;
SET units=stock.units ;
where stock.number=stock_previous.number ;
AND stock.units<>stock_previous.units
endif
ENDSCAN
Next
Reply
View the map of this thread
View the map of this thread starting from this message only
View all messages of this thread
View all messages of this thread starting from this message only