Questions

Update fields in SQL table from Excel spreadsheet

+
0 Votes
Locked

Update fields in SQL table from Excel spreadsheet

billy.watt
I need to update the existing contents of three fields in a table from a spreadsheet, preferably only updating those fields that require to be changed but a blanket update would be fine. The spreadsheet contains 5 fields, the first two of which in combination are the unique keys and are therefore used to select which records in the SQL table are to be updated.

Example data in the spreadsheet:

wh product anal1 anal2 anal3
BG 123456 ABC BCD CDE
FE 234567 ACB BCD DEF
GR 345678 ABC BCD CDE

I know how to import the spreadsheet to a temporary table if that would help, but the update query is the part that I can't figure out.

I'd be grateful for any help at all.
  • +
    0 Votes
    Shellbot

    ok, i presume I don't need to tell you to make a backup of the table and use the backup to test this on :)

    Import that data.
    Now.."The spreadsheet contains 5 fields, the first two of which in combination are the unique keys and are therefore used to select which records in the SQL table are to be updated. "

    Does that mean that your ID in the sql table is "BG123456" (allin one column, in a single string)?

    If so,either transform it on the import so that it makes a field called My_ID, or after you import, create a new col and throw it into there

    Try this..

    UPDATE mySQLTable
    SET mySQLTable.desiredcolumn = myImportTable.desiredcolumn
    FROM mySQLTable
    INNER JOIN myImportTable
    ON mySQLTable.unique_id = myImportTable.my_id

    +
    0 Votes
    billy.watt

    Thanks for the quick and accurate reply. The five fields in the input correspond to five in the SQL table and the index key is a concatenation of the first and second so yes, the key is BG123456. I just concatenate the fields on either side of the 'ON' line to get what I want.
    Thanks again.

    +
    0 Votes
    Shellbot

    did it work ok?

    +
    0 Votes
    billy.watt

    Thanks again.

  • +
    0 Votes
    Shellbot

    ok, i presume I don't need to tell you to make a backup of the table and use the backup to test this on :)

    Import that data.
    Now.."The spreadsheet contains 5 fields, the first two of which in combination are the unique keys and are therefore used to select which records in the SQL table are to be updated. "

    Does that mean that your ID in the sql table is "BG123456" (allin one column, in a single string)?

    If so,either transform it on the import so that it makes a field called My_ID, or after you import, create a new col and throw it into there

    Try this..

    UPDATE mySQLTable
    SET mySQLTable.desiredcolumn = myImportTable.desiredcolumn
    FROM mySQLTable
    INNER JOIN myImportTable
    ON mySQLTable.unique_id = myImportTable.my_id

    +
    0 Votes
    billy.watt

    Thanks for the quick and accurate reply. The five fields in the input correspond to five in the SQL table and the index key is a concatenation of the first and second so yes, the key is BG123456. I just concatenate the fields on either side of the 'ON' line to get what I want.
    Thanks again.

    +
    0 Votes
    Shellbot

    did it work ok?

    +
    0 Votes
    billy.watt

    Thanks again.