OpenBase offers two variations of the insert SQL syntax: INSERT INTO and REPLACE INTO. The SQL standard specifies that INSERT INTO will insert values into a table as a new record. If it is unable to insert the values as a new record an error is returned. REPLACE INTO does the same thing except it will update the values of an existing record if it finds one. REPLACE INTO is an extension to SQL. Both are covered in this section.
INSERT INTO
The INSERT statement is used to create new records and set initial column values. Here is the basic format of the INSERT statement:
INSERT INTO <table> (<column1>, <column2>...) VALUES (<value1>, <value2>...)
Here is sample code of an INSERT statement with a detailed description of each part:
INSERT INTO TALENT (FIRST_NAME, LAST_NAME, TALENT_ID) VALUES ('John', 'Smith', 700)
INSERT INTO TALENT - tells the database that you are inserting a group of values into the TALENT table.
(FIRST_NAME, LAST_NAME, TALENT_ID) specifies the columns you are inserting.
VALUES tells the database that the following values correspond to the columns just specified.
(‘John’ , ‘Smith’ , 700) are the values that correspond to and will be inserted into the FIRST_NAME, LAST_NAME and TALENT_ID columns.
INSERT INTO … SELECT
You can use sub-selects as part of an insert statement. Here is an example:
INSERT INTO TALENT (FIRST_NAME, LAST_NAME, TALENT_ID) VALUES (SELECT FIRST, LAST, TAL_ID FROM ANOTHER_TABLE)
You can also use the output of a stored procedure in your insert statement:
INSERT INTO TALENT (FIRST_NAME, LAST_NAME, TALENT_ID) VALUES (CALL findMyTalents(‘optional parameters’))
REPLACE INTO
The REPLACE INTO statement is used to update or insert database information. When a value with a unique index is specified, it will try to update an existing record. If no record exists it will behave exactly like an INSERT INTO statement. Here is the basic format of the REPLACE INTO statement:
REPLACE INTO <table> (<column1>, <column2>...) VALUES (<value1>, <value2>...)
Here is sample code of an REPLACE INTO statement with a detailed description of each part:
REPLACE INTO TALENT (FIRST_NAME, LAST_NAME, TALENT_ID) VALUES ('John', 'Smith', 700)
REPLACE INTO TALENT - tells the database that you are inserting or updating a record in the TALENT table.
(FIRST_NAME, LAST_NAME, TALENT_ID) specifies the columns or table columns that you are inserting or updating.
VALUES tells the database that the following values correspond to the columns just specified.
(‘John’ , ‘Smith’ , 700) are the values that correspond to and will be inserted into the FIRST_NAME, LAST_NAME and TALENT_ID columns.
REPLACE INTO … SELECT
You can use sub-selects and stored procedure calls as part of a REPLACE INTO statement.
Here is an example of specifying a sub-select:
REPLACE INTO TALENT (FIRST_NAME, LAST_NAME, TALENT_ID) VALUES (SELECT FIRST, LAST, TAL_ID FROM ANOTHER_TABLE)
Here is an example of calling a stored procedure:
REPLACE INTO TALENT (FIRST_NAME, LAST_NAME, TALENT_ID) VALUES (CALL findMyTalents(‘optional parameters’))
Getting the last _rowid value inserted
Sometimes you may have the database auto-generate the _rowid value to be used as a primary key. Immediately after you insert a record, you can get the rowid value used by calling the LASTROWID() function. Here is how it is called:
function LASTROWID()
This returns the rowid value as a standard result set.
