Software

Question

Gravatar
0 Votes
Locked

Update fields in SQL table from Excel spreadsheet

By himself ·
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 ****1 ****2 ****3
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.

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Answers

gravatar
Collapse -

might work

by Shellbot In reply to Update fields in SQL tabl ...

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

gravatar
Collapse -

Short and sweet.

by himself In reply to might work

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.

gravatar
Collapse -

all sorted then

by Shellbot In reply to Short and sweet.

did it work ok?

gravatar
Collapse -

Sure did

by himself In reply to all sorted then
Back to Software Forum

Start or search

Related Discussions

Related Forums