CODEWITHSUNDEEP
mysqlsql
Jade Elizabeth
Jade Elizabeth

Reputation: 133

How do I update values for individual rows quickly?

I have two columns of data in an excel sheet. One is id_upload and the other is id_work. How do I update the works table so that the correct id_work has the right value updated into the column id_upload?

There are 23 columns in the works table so I need to be specific in updating only the id_upload column, and it needs to get the correct row (via id_work).

Here's a sample of the update data:

id_upload  id_work
15           331
16           370
17           689
18           690
19           765

Currently the works table does not have any values in the id_upload field. There's 80 rows of updates to go through and I'd rather not do it manually :).

Upvotes: 0

Views: 63

Answers (1)

Bacon Bits
Bacon Bits

Reputation: 32170

Don't use a single query. That's just asking for trouble. It's not hard to use Excel to create several queries.

Since you neglected to supply the actual schema for your table, I'm assuming the name is smf_works_works, that id_work is the primary and only key, and that id_upload in Excel should be assigned to the id_upload field of smf_works_works. I'm also assuming all fields are integer or numeric fields.

Let column A in Excel be id_upload. Let column B in Excel be id_work. Let row 1 be column headers and row 2 and below be data.

In the column C in Excel, enter this formula:

=CONCATENATE("INSERT INTO smf_works_works (id_work, id_upload) VALUES (",B2,",",A2,") ON DUPLICATE KEY UPDATE id_upload = ",A2,";")

Now fill down column C for your 80 rows. Now select the column, and paste it into a text editor and verify that it looks right. Then paste it into MySQL Workbench or your preferred query analyzer, and run the first row. Use a SELECT statement to verify that the data are correct. Now run the remaining 79 rows.

Upvotes: 1

Related Questions