-1

I have a database with 5 columns plus an Unique Key id. The columns are id (PRIMARY KEY), customer_id. state (ENUM of all 50 US states), type (ENUM of various healthcare practitioner license types), number (varchar(18) license number that is associated with the type), and expires (date for future use).

I accidentally deleted the number field for ONLY one type ( APRN, all other entries in the number column are intact). I last updated a wek ago and so if I can get the number fields from the type APRN back into the live database, I will save myself a great hassle of having to manually enter 5,500 number one at a time.

Statistics from phpMyAdmin are as follows:

Row statistics Format dynamic Collation utf8_general_ci Rows 124,470 Row length 21 B Row size 37 B Next autoindex 135,635 Creation Feb 08, 2022 at 09:42 AM Last update Feb 08, 2022 at 09:42 AM Last check Feb 08, 2022 at 09:50 AM

There would only be 8 total loss APRN number fields and I can reconstruct them manually looking them up on the Florida licensing website.

I CAN update every number field for EVERY type, but not sure how to get the backed up licenses table into the live database to run the UPDATE query on it for on type APRN or all types.

Any help would be most appreciated. Easiest and quickest is preferred.

Henry Geiter
  • 103
  • 1
  • 10
  • How do you delete a column just for some types? Columns are defined at the table level, not by row. – Barmar Feb 08 '22 at 17:07
  • A friend was supposed to run an UPDATE query to REPLACE a specific string in the number column with an empty string when the value in the type column was APRN. Instead he ran an UPDATE query that replaced the contents of the numbers column with an empty string. – Henry Geiter Feb 08 '22 at 17:13
  • It's unlikely that you can recover the previous data without a backup, but maybe a digital recovery service could do something if the table hasn't been modified since then. – Barmar Feb 08 '22 at 17:16
  • Why can't you update just the rows with that type? Load the data from the website into another table, then use `UPDATE + JOIN` with `WHERE type = 'APRN'` – Barmar Feb 08 '22 at 17:18
  • Although if it's just 8 rows, I would just do it by hand, editing those rows. – Barmar Feb 08 '22 at 17:19
  • I have a backup as stated in the OP - " I last updated a week ago and so if I can get the number fields from the type APRN back into the live database" – Henry Geiter Feb 08 '22 at 17:19
  • I do not know how to use " UPDATE + JOIN with WHERE type = 'APRN' " – Henry Geiter Feb 08 '22 at 17:20
  • See https://stackoverflow.com/questions/12394506/mysql-update-table-based-on-another-tables-value – Barmar Feb 08 '22 at 17:21
  • There are 8 NEW APRN number fields that I lost that are NOT part of the backup. So, "I last updated a week ago and so if I can get the number fields from the type APRN back from the backup into the live database, I will save myself a great hassle of having to manually enter 5,500 number field values one at a time." – Henry Geiter Feb 08 '22 at 17:21
  • Sorry, I misunderstood what you wrote in the question. Obviously that needs to be automated. – Barmar Feb 08 '22 at 17:23
  • How to I upload just the licenses table into the live database with a different name so it does not conflict with live licenses table – Henry Geiter Feb 08 '22 at 17:23
  • I do not want to accidentally overwrite the entire number column in the live database – Henry Geiter Feb 08 '22 at 17:24

1 Answers1

0

Load the data from the website into a new table (you can use LOAD DATA INFILE to load from a CSV file into a table). Then merge the missing information into your table:

UPDATE yourTable AS t1
JOIN websiteTable AS t2 ON t1.id = t2.id
SET t1.number = t2.number
WHERE t1.type = 'APRN'

Make sure you make a backup before doing this.

Barmar
  • 741,623
  • 53
  • 500
  • 612
  • It is important to make sure you pick the correct tables for t1 and t2 or the update will make both have empty fields. – Henry Geiter Feb 19 '22 at 08:55