CODEWITHSUNDEEP
mysqlsqlcsvimport-from-csv
scd
scd

Reputation: 164

Update multiple rows with corresponding values from another table MySQL

Using MySQL, I'm working on a script that will import data from a CSV file. I've gotten to the point where the script is finished for importing data for a single user, however that I want to extend to all users now. A statement I currently have is the following:

UPDATE werte 
    SET werte=(SELECT Date_Enrollment 
               FROM THKON01.data 
               WHERE auto_patient_id = 1020)
    WHERE folder_id = 1525 
    AND number=4;

Now what I want is to use the enrollment dates from all users (so I would omit the "WHERE auto_patient_id ..." statement) and insert them into all corresponding rows. Here lies the problem. I tried for two users at once with the statement

UPDATE werte 
    SET werte=(SELECT Date_Enrollment 
               FROM THKON01.data 
               WHERE auto_patient_id = 1020
                   OR auto_patient_id = 1051)
    WHERE folder_id between 1524 AND 1525 
    AND number=4;

However this gave me an error that said "Query returned multiple rows", referring to the inner query of SELECT Date_Enrollment. Note that the auto_patient_id's are not sequentially numbered, so I can't use a "between" there.

EDIT: For clarification

I have two tables. One, werte, is where I want the values to be stored to. THKON01.data is the table I want to read the values from. In case of this example, I want the Date_Enrollment values to be written into the werte table. Let's say I have 3 users I want to do this for, then the structure for THKON01.data looks like this:

auto_patient_id    Date_Enrollment
1020               01.01.1911
1050               02.01.1912
1073               03.01.1913
...                ...

Now I want to insert this into the werte table which looks like this:

folder_id    werte
1525         <empty>
1526         <empty>
1527         <empty>
...          ...

I want them to be inserted so that the first value of THKON01.data (01.01.1911) gets copied to the first value in werte (field of folder_id 1525), the second (02.01.1912) gets to the second (folder_id 1526), and so forth. Folder_id is sequentially numbered, auto_patient_id is not. I hope that clarifies this a little.

Upvotes: 1

Views: 4705

Answers (1)

user15
user15

Reputation: 1054

If you have some links between fields auto_patient_id and folder_id you can try something like this

UPDATE werte 
SET werte=(SELECT Date_Enrollment 
           FROM THKON01.data 
           WHERE (your_link))
WHERE number=4;

here your_link can be of THKON01.data.auto_patient_id = werte.somefield or THKON01.data.auto_patient_id = somefunction(werte.folder_id)

It will select only one record at a time and update all your records that fall under outer where condition.

update

if you want to use some bash script you can use smth like this

$folder_id = 1 # or some other start number
mysql -e "SELECT Date_Enrollment FROM THKON01.data" | while read Date_Enrollment; do
   mysql -e "update werte set werte = $Date_Enrollment where folder_id = $folder_id"
   $folder_id = $folder_id + 1
done

You said that your folder id are in order, so we can just add 1 each time instead of fetching them from result.

I'm absolutely not proficient with bash scripting so this script could be not working, but I hope that the idea is clear.

Upvotes: 2

Related Questions