Table Writes & Updates

Table Writes & Updates

Add new rows or update existing ones to reflect the most up to date state.

Overview

Table writes and updates are only possible to those with the correct permissions. By default, an owner of a table will have typical admin privileges for inserting, updating, and deleting values as well as the ability to set permissions for others. Of course, before writing to a table, it must be created first.

Writing to tables

Insert a Row

Table writes leverage INSERT statement to add rows to an existing table. Consider a table that’s been created with the following schema, called write_table_1:

id int, name text, color text

Without any data, this table doesn’t add all too much value. Let’s change that:

INSERT INTO write_table_1 VALUES (0, 'crow', 'black')

And to be explicit, define exactly which columns to target:

INSERT INTO write_table_1 (id, name, color) VALUES (1, 'fish', 'silver')

Great! Here’s what we now have:

id
name
color
0
crow
black
1
fish
silver

Update Row Values

Now that the table data exists, there might come a time where we want to update its values. As things evolve, so should its data — nothing should have to remain static if it isn’t meant to be! Taking the write_table_1 created and inserted into above, use the UPDATE statement to change a value in a particular row/column, or cell:

UPDATE write_table_1 SET color = 'red' WHERE id = 0

That is, we define the column and SET its value to a new one, overwriting what was previously stored there:

id
name
color
0
crow
red
1
fish
silver

Deletions

If some data no longer belongs in a table, it can be deleted. Note that the rules behind table access control can dictate whether or not this is even possible at the smart contract level, but conceptually, a DELETE for row with fish in it would be:

DELETE FROM write_table_1 WHERE id = 1

Granting & Revoking Permissions

Lastly, changing who can insert rows or update existing ones is a related topic. In the example above, the color red was set to the row for the crow. Unfortunately, crows aren’t red. Whoever made that UPDATE statement was probably (incorrectly) granted permissions using something like the following:

GRANT UPDATE ON write_table_1 TO {address}
/* 
	 Alternatively, maybe there was only access 
   granted for updating the `color` column
*/
GRANT UPDATE (color) ON write_table_1 TO {address}

Not only can these rules be specified for UPDATEs, but INSERT and DELETE privileges are also possible. Regardless, the table owner can revoke these permissions for the specified user’s {address}, which in practice, is an on-chain address (0x…).

REVOKE UPDATE ON write_table_1 FROM {address}

Now, the user who had incorrectly changed the table value no longer has the permissions to update any values.

Implications

Inserting, updating, and deleting data can be controlled by on-chain rules. Thus, a table owner can dictate exactly what can be written to a table or the values it can update. All of this is possible programmatically such that table data is anchored to the blockchain, which defines the rules that then govern who and how the data itself can change.