• 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

• 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 delimiter of identifier in the SQL queries as specified by the SQL92 standard.

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

SELECT

• The DISTINCT keyword is used to delete the duplicate records from the result of the query.
• 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

• a record into a data file.
• several records in 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 find out 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 an HFSQL file into another HFSQL file.
• 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 by 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 the item (chosen among the available types presented below).
  • DEFAULT: is used to define the default value of the item.
  • NOT NULL: is used to prevent from assigning the NULL value to the item.
  • AUTO_INCREMENT: is used to define an automatic identifier for the integer items.
  • COMMENT: is used to define the caption of the item.
  • COLLATE: is used to define 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: is used to specify that the case is ignored.
  • AI: is used to specify that the accented characters are ignored.
  • PI: is used to specify 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.