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).



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: Easily create Help documents