CODEWITHSUNDEEP
mysqlsqlsql-update
user2098185
user2098185

Reputation:

Update MySql table related record relationship without lost data

I have three MySql table

category table:

id        link_id     category_id
--------------------------------------
1          5           10
2          6           12
3          5           12

Link table:

link_id    link_title         link_url        website_id
-------------------------------------------------------
5          title            test.com/test         1
6          title2           test.com/test2        1
7          title3           link.com/linklist     2

Website table:

website_id      url
-----------------------------
1               test.com
2               link.com

now I need to change category table link_id to website_id like this

id        link_id(/website_id)     category_id
--------------------------------------
1          1                    10
2          2                    12
3          1                    12

Is this possible using MySql update query

Thank you in advance!

Upvotes: 1

Views: 6424

Answers (4)

John Woo
John Woo

Reputation: 263803

you can join both tables,

UPDATE  category a
        INNER JOIN link b
            ON a.link_id = b.link_ID
SET     a.link_ID = b.website_ID

Upvotes: 3

Travis G
Travis G

Reputation: 1602

Can you try this ??

update catagory a
set a.link_id = (
  select b.website_id from link b
  inner join website c on b.website_id = c.website_id
    where a.link_id= b.link_id)

Upvotes: 0

Selva Kumar K.P.
Selva Kumar K.P.

Reputation: 1107

UPDATE category
SET link_id = website_id FROM Link L
WHERE L.link_id = category.link_id

Upvotes: 0

Muhammad Raheel
Muhammad Raheel

Reputation: 19882

Try this query but make sure you have backup before doing this.

UPDATE category AS c
  INNER JOIN Link AS l
    ON l.link_id = c.link_id
SET c.link_id = l.website_id

Upvotes: 0

Related Questions