Index...
Centrallix Documentation
|
6.10 INSERT Statements
INSERT Statements
INSERT [ INTO ] pathname select-statement
[ ON DUPLICATE expression [ , ... ]
UPDATE SET expression = expression [, ... ] ]
The currently available way to insert new data into a table in Centrallix is to use the INSERT INTO ... SELECT form of the INSERT statement. In this type of insert, a SELECT statement is specified which provides the data to be inserted - which may be one static row or multiple rows SELECTed from another table or data source.
Here is an example of copying all data from one table to another, assuming that the tables have the same basic schema:
INSERT INTO /data/table2.csv/rows
SELECT * FROM /data/table1.csv/rows
Or, inserting one row using static data:
INSERT INTO /data/table.csv/rows
SELECT first_name="John", last_name="Doe", phone_number="555-1212"
When SELECTing data from another table, it is not necessary to use column name aliases if the columns being selected have the correct names already. It is also not necessary to have the columns in the correct order - in fact Centrallix matches the columns by name, not by position, and so an INSERT INTO ... SELECT statement will not produce expected results if the column names do not match.
As of Centrallix version 0.9.1, INSERT INTO ... SELECT statements can contain a HAVING clause.
The ON DUPLICATE ... clause
To perform an "UPSERT" operation, where an update is done in the event that an inserted object already exists, use the ON DUPLICATE clause. The ON DUPLICATE clause specifies the attributes to check for duplicates (this can be the whole primary key, or just a part of the primary key, or some other unique alternate key, for instance), as well as the SET clause for the update operation.
Example:
INSERT INTO /data/table1.csv/rows
SELECT :key_id, :access_cnt
FROM /data/table2.csv/rows t2
ON DUPLICATE :key_id
UPDATE SET :access_cnt = :access_cnt + :t2:access_cnt
This construction is similar to, but more flexible than, the MySQL "ON DUPLICATE KEY UPDATE" clause. It fills the same role as the standard SQL "MERGE" statement, but is less complex and easier to read.
Other forms of INSERT
Most other databases support such forms of the INSERT statement as "INSERT INTO table VALUES ()", "INSERT INTO table () VALUES ()", and "INSERT INTO table SET ...". Because data sources in Centrallix may return columns/attributes in an arbitrary order, Centrallix does not currently (and may not in the future) support inserting without specifying the exact column names being used. However, other forms of INSERT which do specify column names may be supported in the future.
Comments...
(none yet)
Add a Comment...
|
