Updating MySQL table rows based on data in another column

Question

I have a Magento table that looks like this:

value_id  entity_type_id  attribute_id   entity_id   value
189       2               19               20        MR 
190       2               20               20        Bob
191       2               21               20         
192       2               22               20        Steel 
193       2               23               20         
194       2               24               20         
195       2               26               20        London
196       2               27               20         
197       2               28               20         
198       2               30               20        E84 6DD 
199       2               31               20         
200       2               32               20        01600786546
201       2               36               20         
202       2               19               21         
203       2               20               21        Alice 
204       2               21               21         
205       2               22               21        Smith
206       2               23               21         
207       2               24               21         
208       2               26               21        Manchester
209       2               27               21         
210       2               28               21         
211       2               30               21        M4 56T 
212       2               31               21         
213       2               32               21        01278 865214 

What I need to do is move the phone number up one row - so where the attribute_id is equal to 32, I need to move the value field of that row into the value field where the attribute_id is equal to 31.

I imagine I'll need to set some variables, but I have no idea where to begin. Any guidance would be much appreciated!

Answer 1

Adapt this query by replacing the name of your_table:

UPDATE your_table t1
INNER JOIN your_table t2
SET t1.value = t2.value
WHERE t1.attribute_id = 31
AND t2.attribute_id = 32
AND t1.entity_id = t2.entity_id
AND t1.entity_type_id = t2.entity_type_id;