Scroll to navigation

STILTS-SQLUPDATE(1) Stilts commands STILTS-SQLUPDATE(1)

NAME

stilts-sqlupdate - Updates values in an SQL table

SYNOPSIS

stilts sqlupdate [db=<jdbc-url>] [user=<value>] [password=<value>] [select=<select-stmt>] [assign=<col>=<expr>] [progress=true|false]

DESCRIPTION

sqlupdate updates values in an existing table in an SQL database. The rows to update are specified, as a normal SELECT statement, using the select parameter. Each column to update, and the value to write to it, are given using the assign parameter.

Why not just use the database's own UPDATE statement? In most cases, that would be a much better idea. However, using sqlupdate you can write values using STILTS's expression language, and hence take advantage of its various functions, without having to embed them into the database. SQL column names can be used as variables in these expressions, in the same way that table column names are used as variables in other commands such as tpipe.

This command can only be used if you have access to an SQL database via JDBC. The details of how to configure a JDBC connection to a database are discussed in SUN/256 - obviously you will need a database to connect to and appropriate write permissions on it as well as the relevant drivers.

This is a somewhat specialised command, and several (database/driver-specific) things can go wrong with it. If you do not have a fairly good understanding of the database with which you are using it then you may run into problems (but then you'd be unlikely to have the permissions to do the updates in any case).

OPTIONS

URL which defines a connection to a database. This has the form jdbc:<subprotocol>:<subname> - the details are database- and driver-dependent. Consult Sun's JDBC documentation and that for the particular JDBC driver you are using for details. Note that the relevant driver class will need to be on your classpath and referenced in the jdbc.drivers system property as well for the connection to be made.

User name for logging in to SQL database. Defaults to the current username.

Password for logging in to SQL database.

Gives the full text (including "SELECT") of the SELECT statement to identify which rows undergo updates.

Assigns new values for a given column. The assignment is made in the form <colname>=<expr> where <colname> is the name of a column in the SQL table and <expr> is the text of an expression using STILTS's expression language, as described in SUN/256. SQL table column names or $ID identifiers may be used as variables in the usual way.

This parameter may be supplied more than once to effect multiple assignments, or multiple assignments may be made by separating them with semicolons in the value of this parameter.

If true, a spinner will be drawn on standard error which shows how many rows have been updated so far.

SEE ALSO

stilts(1)

If the package stilts-doc is installed, the full documentation SUN/256 is available in HTML format:
file:///usr/share/doc/stilts/sun256/index.html

VERSION

STILTS version 3.4.9-debian

This is the Debian version of Stilts, which lack the support of some file formats and network protocols. For differences see
file:///usr/share/doc/stilts/README.Debian

AUTHOR

Mark Taylor (Bristol University)

Mar 2017