Question 295645 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Replace string with foreign key

I have old large table logs with columns type, message and others. Column type – varchar

LOGS

type message
alert Send mail notify
shop Sell product1
alert Send push notify
forum New user
shop F*cked up on shop_module
shop Sell product2

How can I make this table look like this?

LOGS

type message
1 Send mail notify
2 Sell product1
1 Send push notify
3 New user
2 F*cked up on shop_module
2 Sell product2

LOGS_TYPE

id type
1 alert
2 shop
3 forum

I only know how to do this using a PHP script. But can I only use MySQL?

MYSQL 8.0.30-22

UPDATE

I don’t have a logs_type table yet and I want to create one based on the logs and then return a foreign key there. Can I do this in one request?

Tags:

2

Answers


  1. 0

    I think you have your own response,when you have created that intermediate table (logs_type),
    and you need to search for the data, you can link both tables with INNER JOIN, something like:

    SELECT lt.type AS type ,l.message AS message 
FROM LOGS AS l 
  INNER JOIN 
LOGS_TYPE AS lt
  ON l.type = lt.id

    I don’t know if that solves your problem, hope it helps!

    And if you want to overwrite your actual logs tables, i would try something like:

    UPDATE LOGS SET type=1 WHERE type = 'alert';
UPDATE LOGS SET type=2 WHERE type = 'shop';
UPDATE LOGS SET type=3 WHERE type = 'forum';

    And then you will overwrite that column with the new values corresponding to that LOGS_TYPE table you have just created.

    Login or Signup to reply.
  2. 0

    You can do it as follows :

    UPDATE `LOGS` l
INNER JOIN LOGS_TYPE t ON t.type = l.type
SET l.type = t.id

    It is preferable then to change data type of column type to TINYINT :

    ALTER TABLE `LOGS` MODIFY type TINYINT;

    Demo here

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search