CODEWITHSUNDEEP
phpmysqlsqlphpmyadminsql-update
RavatSinh Sisodiya
RavatSinh Sisodiya

Reputation: 1608

Mysql how to copy data from one field to other field in same table

I have mysql table as follow:

    id      host             url
--------------------------------------
    1                   test.com/index.php
    2                   domain.com/list
    3                   www.stackoverflow.com/questions/ask

I need output as follow

    id      host                 url
------------------------------------------
    1     test.com             test.com/index.php
    2     domain.com           domain.com/list
    3     stackoverflow.com    www.stackoverflow.com/questions/ask

Thanks in advance.

Upvotes: 0

Views: 891

Answers (3)

Iswanto San
Iswanto San

Reputation: 18569

Use SUBSTRING_INDEX

UPDATE tbl1 SET host = SUBSTRING_INDEX(url, '/', 1)

Upvotes: 0

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79899

Use SUBSTRING_INDEX(str, delim, count):

UPDATE tablename
SET host = SUBSTRING_INDEX(url, '/', 1);

SQL Fiddle Demo

This will make your table looks like:

| ID |                  HOST |                                 URL |
--------------------------------------------------------------------
|  1 |              test.com |                  test.com/index.php |
|  2 |            domain.com |                     domain.com/list |
|  3 | www.stackoverflow.com | www.stackoverflow.com/questions/ask |

Upvotes: 4

Mikhail Vladimirov
Mikhail Vladimirov

Reputation: 13890

Assuming your table is named t:

UPDATE t
SET host=SUBSTRING_INDEX(url, '/', 1);

Upvotes: 2

Related Questions