CODEWITHSUNDEEP
mysql
user1942760
user1942760

Reputation: 13

Copying data from a one row to another in the same column for specified rows

I have found solutions for questions similar to that which I am asking but so far none have fully resolved my problem, so please forgive me if there is a small amount of repetition.

I have a table meta_data which contains the columns project, stage, function, m_char_2 and id

meta_data
project |  stage    |  function  | m_char_2  |  id     |
 519    | reception |    notes   |  11/1272  | 2363827 |
 519    | reception | diagnosis_1|           | 2363827 |
 519    | reception |    notes   |  11/1278  | 2370589 |
 519    | reception | diagnosis_1|           | 2370589 |
 519    | reception |    notes   |           | 2398654 |
 519    | reception | diagnosis_1|  13/2586  | 2398654 |

I wish to copy the values in column m_char_2 where the function value = notes to m_char_2 where function value = diagnosis_1 for the corresponding id.

I have written a syntax that allows me to do this for individual ids that works fine

UPDATE meta_data 
SET m_char_2 =
  (SELECT m_char_2
   FROM (SELECT * FROM meta_data) AS md
   WHERE
     md.stage ='Reception'
     AND md.function='Notes'
     AND md.id = 2363827)
WHERE
  project=519
  AND stage='Reception'
  AND function ='Diagnosis_1'
  AND id = 2363827;

However, I can not get it to work for multiple ids. I have over 100 records to update so don't want have to have to run a query for each id entry.

This does not work:

UPDATE meta_data 
SET m_char_2 =
  (SELECT m_char_2
   FROM (SELECT * FROM meta_data) AS md
   WHERE
     md.stage ='Reception'
     AND md.function='Notes'
     AND md.id IN (2363827,2370589))
WHERE
  project=519
  AND stage='Reception'
  AND function ='Diagnosis_1'
  AND id IN (2363827,2370589);

It returns the error #1242 subquery returns more than 1 row.

Any suggestions?

Upvotes: 1

Views: 202

Answers (2)

user645280
user645280

Reputation:

Just for fun, I don't think it needs a JOIN:

UPDATE meta_data AS mn
  SET m_char_2 = (
    SELECT m_char_2
    FROM (SELECT * FROM meta_data) AS md
    WHERE md.stage ='Reception' AND md.function='Notes'
      AND md.id = mn.id
    ) 
  WHERE project=519 AND stage='Reception'
    AND function ='Diagnosis_1' AND isnull(m_char_2)

Upvotes: 1

fthiella
fthiella

Reputation: 49049

Here I am joining meta_data with itself, and updating all rows with diagnosis_1 with the values taken from notes:

UPDATE
  meta_data m1 inner join meta_data m2
  on m1.id=m2.id
     and m1.project=m2.project
     and m1.stage=m2.stage
     and m1.function='Notes'
     and m2.function='diagnosis_1'
SET
  m2.m_char_2=m1.m_char_2
WHERE
  m1.project=519
  AND m1.stage='Reception'
  AND m1.id IN (2363827,2370589);

(see this fiddle). If you also need to keep existing values like '13/2586', you might want to add some other conditions, like:

and (m2.m_char_2 IS NULL or LENGTH(m2.m_char_2)=0)
and m1.m_char_2 IS NOT NULL and LENGTH(m1.m_char_2)>0

Upvotes: 2

Related Questions