0

I'm new to MySQL and am trying to do the following:

I have a table 'links' that with a new column called 'project_id", and an existing column called 'project'. I have a second table called 'projects', with a column called 'id'.

I need to use the value of 'links > project' to loop over the corresponding id 'projects > id', and then insert it into 'links > project_id'. Once that is done, we will no longer need 'links > project', as 'links > project_id' will become a foreign key and we can then simply look it up in 'projects'.

My pseudo sql code (I'm brand new to this) looks like:

INSERT INTO links(project_id)
select id 
from projects
where project = links(project_id)

Thanks (apologies in advance for being a newb)

dbonneville
  • 2,079
  • 4
  • 19
  • 26
  • Do you want the new project_id column to be a new record all on its own, or do you want to fill in the column on existing records? – Daniel Bernsons Mar 20 '17 at 00:32
  • The new links(project_id) column is net new for existing rows. The values from project(id) needs to fill links(project_id) based on the the value of links(project). – dbonneville Mar 20 '17 at 00:37
  • So the project Id is already in the links table under project? Otherwise, what is the content of the project column? – Daniel Bernsons Mar 20 '17 at 00:39
  • The project id (int) is newly created in the links table. There is a project (varchar) column in links we want to no longer use that has the project name. Since we now have a projects table (with id, project), we want to read each row of links, get links(project), pass that value into a subquery, to then get the value of projects(id) where projects(project) = links(project), and INSERT that value back up to links(project_id). That way, we can kill off links(project) VARCHAR and only have links(project_id). – dbonneville Mar 20 '17 at 00:46

2 Answers2

0

This is possible, but assumes that each project.project value exactly matches the links.project field.

UPDATE links AS l
INNER JOIN (
    SELECT Id, project FROM project
    ) AS a ON a.project = l.project
SET l.project_id = p.Id

This will update the project_id field on the links table with the corresponding id from the projects table that matches the value in links.project.

If you want to create a new record, use the following

INSERT INTO links (project_id)
SELECT Id FROM project p 
INNER JOIN links l ON l.project = p.project

In both cases, the INNER JOIN will make sure that only records from project that already have an equivalent record in links will be used for this operation.

Daniel Bernsons
  • 650
  • 7
  • 20
-1

Yes, absolutely, but check the syntax : 

INSERT INTO tbl_temp2 (fld_id)
SELECT tbl_temp1.fld_order_id
FROM tbl_temp1 WHERE tbl_temp1.fld_order_id > 100;

https://dev.mysql.com/doc/refman/5.7/en/insert-select.html

INSERT with SELECT

Community
  • 1
  • 1
Ali Ben Messaoud
  • 11,690
  • 8
  • 54
  • 87