MySqlConnect: Difference between revisions

MySqlConnect (view source)

Revision as of 13:18, 22 August 2022

67 bytes added , 13:18, 22 August 2022

m

→‎Populating missing/incorrect columns in existing rows: added cross-ref to forum

Sfws

5,838

edits

@@ Line 353: / Line 353: @@
 To update one or more columns in multiple rows, you need to generate a succession of UPDATE queries, keeping the text to send to the database server as short as possible.
-One way to do this is to open the relevant file using a spreadsheet (e.g. Libre Office has a "calc" option, this is free and available for most operating systems).   Create an extra column after existing columns in the spreadsheet for the "primary key",  you should be able to generate this from the first one or two columns of the spreadsheet with some manipulation.  Now "Hide" all the columns except those with data that you want to include in the update, and the primary key column.  With some spreadsheet skills you can generate the required SQL in this format:
+One way to do this is to open the relevant file using a spreadsheet (e.g. Libre Office has a "calc" option, this is free and available for most operating systems).   Create an extra column after existing columns in the spreadsheet for the "primary key",  you should be able to generate this from the first one or two columns of the spreadsheet with some manipulation.  Now "Hide" all the columns except those with data that you want to include in the update, and the primary key column.  With some spreadsheet skills (see https://cumulus.hosiene.co.uk/viewtopic.php?p=165767#p165767) you can generate the required SQL in this format:
 <pre>
 UPDATE name-of-table SET first-column-name=first-row-and-first-column-value, second-column-name=first-row-and-second-column-value WHERE primary-key-column-name=first-row-primary-key-value;