CODEWITHSUNDEEP
mysql
Riya Shah
Riya Shah

Reputation: 167

Find and Move Data From One Column To Another in MySQL Database

I have a database column like

id, title, phone, email, wesbite

Since there some issue in data entry, phone column contains some email address which I want to move to email column, I am able to find all rows from phone column using below query

SELECT * FROM `data` WHERE phone LIKE '%@%'

But I do not know how to move ( NOT Copy ) to it in the email column.

Upvotes: 1

Views: 38

Answers (1)

GMB
GMB

Reputation: 222722

You want an update statement:

update data 
set email = phone, phone = null
where phone like '%@%'

For the selected rows, this sets email from phone, and sets phone to null.

It should be noted that in MySQL - unlike most other databases - the ordering of the assignments is important in such a query. If we were to assign a null value to phone first, then it would propagate to email.

Upvotes: 1

Related Questions