sql_update

Commands ››
Parent Previous Next

The sql_update command generates SQL UPDATE statements from CSV data. To be useful, such statements require a WHERE clause, so the sql_update command provides means of specifying the table to update, the columns to change and the WHERE clause to use to locate the row(s) to be updated.

See also: sql_delete, sql_insert

Flag

Req'd?

Description

-t table

Yes

Specifies the name of the SQL table to use in the UPDATE statement.

-f fields

Yes

Specifies a list of field index/field name pairs to use to generate the SET clause of the SQL statement. The list is comma-separated, with each pair being colon-separated. For example:

-f 1:name,2:rank,5:serialno

specifies that field 1 will be called 'name', field 2 will be called 'rank' and field 5 will be called 'serialno'. Fields 3 and 4, which are not mentioned in the list, will be excluded from the generated SET clause.

-w fields

Yes

Specifies the fields that will be used to generate the WHERE clause of the UPDATE statement. The format is the same as that used in the -f flag, described above.

-s separator

No

Specifies the separator that will be appended to the end of each statement. By default this is a new line followed by a semicolon. If your database requires COMMITs after each insert, you could use something like this:

-s '\n;\nCOMMIT\n\;\n'

-nq fields

No

Turns off SQL quoting. See the sql_insert command for full description.

-qn

No

Specifies that the special value NULL should be quoted. By default CSVfix does not quote the NULL string (in whatever case).

-en

No

Convert empty CSV fields to NULL



The following example generates UPDATE statements. We assume that the file names.dat contains a list of people we want to send a new mail shot out to, and that we are updating a SQL table that looks like this:

CREATE TABLE mailing (
    fname VARCHAR(32),
    sname VARCHAR(32),
    need_mail CHAR
)

the CSVfix command line to do this is:

csvfix pad -n 4 -p 'Y'  data/names.csv |    \
csvfix sql_update -t mailing -f 4:need_mail -w 1:fname,2:sname

This works by using the pad command to append a 'Y' field to all rows and then feeds the modified data into the sql_update command. The resulting output is:

UPDATE mailing SET need_mail = 'Y' WHERE fname = 'Charles' AND sname = 'Dickens'
;
UPDATE mailing SET need_mail = 'Y' WHERE fname = 'Jane' AND sname = 'Austen'
;
UPDATE mailing SET need_mail = 'Y' WHERE fname = 'Herman' AND sname = 'Melville'
;
UPDATE mailing SET need_mail = 'Y' WHERE fname = 'Flann' AND sname = 'O''Brien'
;
UPDATE mailing SET need_mail = 'Y' WHERE fname = 'George' AND sname = 'Elliot'
;
UPDATE mailing SET need_mail = 'Y' WHERE fname = 'Virginia' AND sname = 'Woolf'
;
UPDATE mailing SET need_mail = 'Y' WHERE fname = 'Oscar' AND sname = 'Wilde'
;
 

Created with the Personal Edition of HelpNDoc: iPhone web sites made easy