• SELECT
• INSERT
• UPDATE
• DELETE
• CREATE TABLE
• CREATE TABLE AS
• DROP TABLE
• ALTER TABLE
• CREATE INDEX
• DROP INDEX
• GRANT
• REVOKE
• CREATE VIEW
• DROP VIEW
• CREATE MATERIALIZED VIEW
• DROP MATERIALIZED VIEW
• REFRESH MATERIALIZED VIEW
• New in version 28
  OPTIMIZE TABLE

• These statements can be used:
  • in the SQL code of queries created in the query editor. Then, these queries will be run by HExecuteQuery.
  • in the SQL code of queries run by HExecuteSQLQuery.
• The " character can be used as an identifier delimiter in SQL queries as specified by the SQL92 standard.

	Important	The query editor cannot be used to create queries in SQL using the INSERT, UPDATE or DELETE commands.

SELECT

• The DISTINCT keyword deletes the duplicate records from the query result.
• For more details on selection conditions, see Selection conditions in an SQL query.
• A Select query can be created in the query editor. For more details, see Creating a select query.
• In the syntax of the SELECT statement, the NameOfItems parameter can contain one or more selection conditions. For example:
  
  SELECT CustomerLastName, Age>18 AS Adult FROM Customer

Running a stored procedure

• The stored procedure must be a stored function: it must always return a value. Otherwise, a runtime error will occur.
• If the name of the stored procedure is identical to the name of a function or procedure found in the project, its name must be prefixed by the name of the set of stored procedures.
• The result of a stored procedure can also be one of the parameters of a SELECT query.

INSERT

• one record to a data file.
• multiple records to a data file.

• a new customer in the CUSTOMER file (the complete customer information is provided).
  
  INSERT INTO CUSTOMER
  VALUES (77, 'Moore', 'Julia', '1 rue Giono',...
  '69000', 'Lyon')
• a new customer in the CUSTOMER file: only the customer's number, last name and first name are provided:
  
  INSERT INTO CUSTOMER (CustomerNum, CustomerLastName, CustomerFirstName)
  VALUES (77, 'Moore', 'Julia')
• 3 products in a PRODUCT file:
  
  INSERT INTO PRODUCT
  VALUES ('Ref01', 'Mineral water'), ('Ref02', 'Butter'), ('Ref03', 'Sponge')

• An Insert query can be created in the query editor. For more details, see Creating an insert query.
• To get the value of the automatic identifier in an INSERT query on HFSQL files (query run by HExecuteSQLQuery, HExecuteQuery or SQLExec), use the SQL.IDAuto variable.
• The INSERT command cannot be used to copy data from one HFSQL file to another.
• The H.NbRecModificationQuery variable is used to find out the number of inserted records.

INSERT with selection

• The number of items to insert must be identical to the number of values returned by the SELECT query.
• All the files items are taken into account if no item is specified in the INSERT statement.
• This type of query cannot be created in the query editor.

• The following SQL code is used to modify the title and the name of the customer named Montgomery:
  
  UPDATE CUSTOMER
  SET Title = 'Mrs',
  CustomerLastName = 'Darwin'
  WHERE CustomerLastName = 'Montgomery'
• The following SQL code modifies the client name in the Customer file. For records with the same ID, the name of the client in the Customer file is replaced with the name in the Customer_Svg file:
  
  UPDATE CUSTOMER, CUSTOMER_SVG
  SET CUSTOMER.NAME = CUSTOMER_SVG.NAME
  WHERE CUSTOMER.ID = CUSTOMER_SVG.ID

• The WHERE clause accepts the sub-selections (by SELECT), if the sub-selection is relative to another file.
• The SET expression accepts no sub-selection (no SELECT command just after SET). Similarly, no join can be performed in the UPDATE section of the query.
  The following query is accepted:
  
  UPDATE PRODUCT SET SalesPrice = 1000
  WHERE ProductRef IN (SELECT ORDERS.ProductRef FROM ORDERS)
  -- Sub-query in WHERE: OK
  
  The following query is not accepted:
  
  UPDATE Product
  SET SalesPrice = (SELECT AVG(ORDERS.UnitPrice) FROM ORDERS
  -- Sub-query in SET: NOT VALID
  WHERE ORDERS.ProductRef = PRODUCT.ProductRef
  GROUP BY ORDERS.ProductRef)
  WHERE ProductRef IN (SELECT ORDERS.ProductRef FROM ORDERS)
  
  Similarly, the following query is not accepted:
  
  UPDATE Article INNER JOIN PRODUCT ON (Article.ArticleID = Product.ProductID)
  -- Join in UPDATE: NOT VALID
  SET Article.SalesPrice = Product.SalesPrice
  WHERE ProductRef IN (SELECT ORDERS.ProductRef FROM ORDERS)
• An Update query can be directly created in the query editor. For more details, see Creating an update query.
• The quote is used as delimiter for the strings in the SQL queries. If the value to write must contain a quote, this value must be preceded by the backslash character ('\\'). For example:
  
  UPDATE CUSTOMER
  SET Address = 'Rue de l\'écluse'
  WHERE CustomerLastName = 'Montgomery'
• The H.NbRecModificationQuery variable is used to find out the number of updated records.

• the "Description of item" parameters correspond to:
  
  ItemName type  [DEFAULT 'value' | DEFAULT NULL] [NOT NULL]
    [UNIQUE] [PRIMARY KEY]  [AUTO_INCREMENT] [COMMENT 'caption'] [COLLATE 'language']
  where:
  • ItemName: name that will be given to the item.
  • Type: Type of item (chosen among the available types presented below).
  • DEFAULT: defines the default value of the item.
  • NOT NULL: prevents from assigning the NULL value to the item.
  • UNIQUE: defines an item with or without duplicates.
  • PRIMARY KEY: defines a primary key.
  • AUTO_INCREMENT: defines an automatic identifier for items of type Integer.
  • COMMENT: defines the caption of the item.
  • COLLATE: defines the language for sorting the item (for example: 'EN_US').
• the "Description of index" parameters correspond to:
  
  INDEX IndexName ( itm1 [ASC | DESC] [CI] [AI] [PI], itm2 ... ) [UNIQUE]
  where:
  • ASC and DESC: are used to define the sort direction.
  • CI: specifies that the case is ignored.
  • AI: specifies that accented characters are ignored.
  • PI: specifies that the punctuation is ignored.

Available types

• IF EXISTS is used to avoid returning an error if the table does not exist.
• This type of query cannot be created with the wizard found in the query editor.