AMEND (bSQL) | Blockpoint Docs

Modifies a single record or records. Amending a record whose primary key doesn't exist in the blockchain will error. For examples, see Examples.

bSQL Syntax Conventions.

Syntax

-- Syntax for AMEND - Blockchain Mutation 
  
AMEND  
{  
    <object>
    {  
        [ ( column_list ) ]   
        { VALUES ( [ ,...n ] ) [ , ( [ ,...n ] )... ]     
        }  
    }  
}  
  
<object> ::=  
{   
   database_name.blockchain_name   
   | blockchain_name    
} 

-- Syntax for AMEND INTO - Blockchain Mutation

AMEND INTO <target_blockchain> 
     <select_statement>

<target_blockchain> ::= 
{ database_name.schema_name.blockchain_name |
 schema_name.blockchain_name |
 blockchain_name }

<select_statement> ::=
 SELECT *select_list*
 [ FROM *blockchain_source* | <import_statement> ] [ WHERE *search_condition* ]  
 [ GROUP BY *group_by_expression* ]  
 [ HAVING *search_condition* ]  
 [ ORDER BY *order_expression* [ ASC | DESC ] ]  
 [ FILTER *filter_expression* ] 

<import_statment> ::=
   IMPORT = <filepath> 
   **schema definition**

Arguments

INTO
Is an optional keyword that can be used between AMEND and the target blockchain.

database_name
Is the name of the database.

blockchain_name
Is the name of the blockchain that is to receive the data.

(column_list)
Is a list of one or more columns in which to amend data. column_list must be enclosed in parentheses and delimited by commas.

If a column is not in column_list, the schema must be able to provide a value based on the definition of the column; otherwise, the row cannot be loaded. EXCEPT for blockchains of SPARSE type; these blockchains infer the unspecified value from the previous version of the record based off of primary key.

The system automatically provides a value for the column if the column:

• Has an autoincrement property. The next incremented identity value is used.
• Has a default. The default value for the column is used.
• Has a timestamp data type. The current timestamp value is used.
• Is nullable. A null value is used.

VALUES

• Introduces the list or lists of data values to be inserted. There must be one data value for each column in column_list, if specified, or in the blockchain. The value list must be enclosed in parentheses.
• If the values in the Value list are not in the same order as the columns in the blockchain or do not have a value for each column in the blockchain, column_list must be used to explicitly specify the column that stores each incoming value.
• If the schema contains default, autoincrement, or null columns the columns must be specified to avoid ambiguous amends.

AMEND INTO
Amends the results of SELECT statement into the target.

• It must be a base blockchain.
• The resulting data must be compatible with the blockchain schema.

Data Types

When you amend rows, consider the following data type behavior:

• If a value is being loaded into columns with a string or byte array data type, the padding or truncation of trailing blanks (spaces for PACKED string, zeros for PACKED byte array).
  

Data type	Default operation
string	Pack the value to the size of the string inserted.
byte array	Pack the byte array to its length.

Limitations and Restrictions

• AMEND is not supported for HISTORICAL blockchains.
• The records amended must share a primary key with a record in the target blockchain.

Permissions

AMEND or WRITE permissions required on the target blockchain .

Both AMEND and WRITE permissions default to members of the admin and developer fixed database roles. Members of the admin fixed database role can transfer permissions to other users.

When amending into a blockchain container from a select statement the user must also have READ permissions on the query.

Examples

|Category|Featured syntax elements| |--------------|------------------------------| |[Basic syntax](#BasicSyntax)|AMEND • blockchain value constructor| |[Other Source](#OtherSources)|AMEND INTO| ### Basic SyntaxExamples in this section demonstrate the basic functionality of the AMEND statement using the minimum required syntax. #### A. Amending a single record The following example amends a single record. The values associated with the primary key `MMM` will be "replaced" by the new record. This example uses the Financial [Demo Database](/docs/demo_database). ```sql AMEND financial.pricing VALUES ("MMM", 140, 2.06, 19.95, 6.72, 26.386, 102.89, 140.43); ``` #### B. Amending multiple records of data The following example updates the two records associated with the primary keys `MMM` and `ABT`.

This example uses the Financial Demo Database.

AMEND financial.pricing
    VALUES ("MMM", 140, 2.06, 19.95, 6.72, 26.386, 102.89, 140.43),
    ("ABT",  40.0, 1.61, 24.48, 1.623, 16.259, 32.7, 40.49);

C. Amending data that is not in the same order as the blockchain columns

The following example uses a column list to explicitly specify the value to be amended.

This example uses the Financial Demo Database.

AMEND financial.companies (name, symbol, sector)
     VALUES ("3M Co.","MMM","Ind.");

Other Sources

Examples in this section illustrate updating records in a blockchain from sources other than raw values.

A. Amending data from another blockchain

The following example updates the record inserted into the current_price using the result of the SELECT statement.

This example uses the Financial Demo Database.

CREATE BLOCKCHAIN financial.current_price
    TRADITIONAL (symbol STRING PACKED PRIMARY, price FLOAT32);

INSERT financial.current_price VALUES ("MMM", 0);

AMEND INTO financial.current_price
    SELECT symbol, price
    FROM financial.pricing
    WHERE symbol = "MMM";

A. Inserting data from a file

The following example amends all the data that satisfies the query from constituents-fianancial.csv in the examples directory into the financial.raw blockchain. The schema defined in the blockchain must correspond to the schema defined for the import and every record amended must share a primary key with a value already in the blockchain.

CREATE BLOCKCHAIN financial.current_price
    TRADITIONAL (symbol STRING PACKED PRIMARY, price FLOAT32);

INSERT financial.current_price VALUES ("MMM", 0);

AMEND INTO financial.current_price
    SELECT symbol, price
    FROM  IMPORT = "exampls/constituents-financials.csv" 
    (symbol STRING PADDED PRIMARY, name string PADDED,
     sector STRING PADDED, price FLOAT32, price_earning FLOAT32,
     dividend_yield FLOAT32, earning_share FLOAT32,  book_value FLOAT32,
     52_week_low FLOAT32, 52_week_high FLOAT32, market_cap FLOAT64,
     ebitda FLOAT64, sales FLOAT64, price_book_value FLOAT32,
     sec_filings STRING PADDED)
    WHERE symbol = "MMM";

Important File types supported are currently .json, .csv, and .html files.

See Also

• INSERT
• DISCONTINUE