Styjun

Integral of the integral using NIntegrate

What to do when you reach a conclusion and find out later on that someone else already did?

What happens if you cast Animated Objects on an weapon affected by Shillelagh?

How do professional electronic musicians/sound engineers combat listening fatigue?

This message is flooding my syslog, how to find where it comes from?

Invert Some Switches on a Switchboard

Timing/Stack question about abilities triggered during combat

Why are so many countries still in the Commonwealth?

What does "see" in "the Holy See" mean?

Reduce column width of table while also aligning values at decimal point

How do I generate distribution of positive numbers only with min, max and mean?

What causes long-running disputes over sovereignty?

powerhouse of ideas

How can I prevent corporations from growing their own workforce?

How do I stop my characters falling in love?

What are the missing letters?

What is the lowest-speed bogey a jet fighter can intercept/escort?

Send a single HTML email from Thunderbird, overriding the default "plain text" setting

Automatic Habit of Meditation

Can the 2019 UA Artificer's Returning Weapon and Radiant Weapon infusions stack on the same weapon?

How to get the two pictures aligned

What should I say when a company asks you why someone (a friend) who was fired left?

What exactly makes a General Products hull nearly indestructible?

High income, sudden windfall

Best MySQL way to move data from table to another one with junction

Which MySQL data type to use for storing boolean valuesWhat is the best collation to use for MySQL with PHP?Should I use the datetime or timestamp data type in MySQL?Duplicating a MySQL table, indices, and dataInsert into a MySQL table or update if existsNHibernate retrieve data from multiple tables in mysqlHow to get the sizes of the tables of a MySQL database?Get data from multiple tables in MySQLSingle SQL to aggregate data from three tablesjoin sql query based on value of another table (in one query)

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty margin-bottom:0;

0

I have a SQL table called members

| member_id | first_name | last_name | email | password | phone_number | country |
|-----------|------------|-----------|----------------------------|----------|--------------|---------|
| 1 | John | Doe | john.doe@rambo.vid | 123456 | 654789159 | en_US |
| 2 | Jules | Ferry | jules.ferry@politician.old | 456789 | | fr_FR |

I would like to create a new table users and move data with user_id as junture like this :

users

| user_id | first_name | last_name | email | password |
|---------|------------|-----------|----------------------------|----------|
| 1 | John | Doe | john.doe@rambo.vid | 123456 |
| 2 | Jules | Ferry | jules.ferry@politician.old | 456789 |

members

| member_id | user_id | phone_number | country |
|-----------|---------|--------------|---------|
| 1 | 1 | 654789159 | en_US |
| 2 | 2 | | fr_FR |

What is the best way (more efficient execution) in MySQL to modify the existing database ?

mysql

share|improve this question

asked Mar 26 at 17:10

J.BizMaiJ.BizMai

84422 gold badges88 silver badges2222 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Have you looked at INSERT INTO SELECT? dev.mysql.com/doc/refman/8.0/en/insert-select.html
  
  – clinomaniac
  Mar 26 at 17:27
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Yes but, I need after to do a loop for each data row and get user_id and save user_id in members table. I do not know if there is an easier way in only one query.
  
  – J.BizMai
  Mar 26 at 18:23
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Not sure why you need to loop. Can you explain another way? Why can't it be 2 insert statements?
  
  – clinomaniac
  Mar 26 at 21:27
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Where do you get the user_id from? Is it another table? You can join them if that's the case. You can run an Update statement after you are done inserting into the users table.
  
  – clinomaniac
  Mar 26 at 21:30
  

  
  
  
  

add a comment | 

0

I have a SQL table called members

| member_id | first_name | last_name | email | password | phone_number | country |
|-----------|------------|-----------|----------------------------|----------|--------------|---------|
| 1 | John | Doe | john.doe@rambo.vid | 123456 | 654789159 | en_US |
| 2 | Jules | Ferry | jules.ferry@politician.old | 456789 | | fr_FR |

I would like to create a new table users and move data with user_id as junture like this :

users

| user_id | first_name | last_name | email | password |
|---------|------------|-----------|----------------------------|----------|
| 1 | John | Doe | john.doe@rambo.vid | 123456 |
| 2 | Jules | Ferry | jules.ferry@politician.old | 456789 |

members

| member_id | user_id | phone_number | country |
|-----------|---------|--------------|---------|
| 1 | 1 | 654789159 | en_US |
| 2 | 2 | | fr_FR |

What is the best way (more efficient execution) in MySQL to modify the existing database ?

mysql

share|improve this question

asked Mar 26 at 17:10

J.BizMaiJ.BizMai

84422 gold badges88 silver badges2222 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Have you looked at INSERT INTO SELECT? dev.mysql.com/doc/refman/8.0/en/insert-select.html
  
  – clinomaniac
  Mar 26 at 17:27
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Yes but, I need after to do a loop for each data row and get user_id and save user_id in members table. I do not know if there is an easier way in only one query.
  
  – J.BizMai
  Mar 26 at 18:23
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Not sure why you need to loop. Can you explain another way? Why can't it be 2 insert statements?
  
  – clinomaniac
  Mar 26 at 21:27
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Where do you get the user_id from? Is it another table? You can join them if that's the case. You can run an Update statement after you are done inserting into the users table.
  
  – clinomaniac
  Mar 26 at 21:30
  

  
  
  
  

add a comment | 

I have a SQL table called members

| member_id | first_name | last_name | email | password | phone_number | country |
|-----------|------------|-----------|----------------------------|----------|--------------|---------|
| 1 | John | Doe | john.doe@rambo.vid | 123456 | 654789159 | en_US |
| 2 | Jules | Ferry | jules.ferry@politician.old | 456789 | | fr_FR |

I would like to create a new table users and move data with user_id as junture like this :

users

| user_id | first_name | last_name | email | password |
|---------|------------|-----------|----------------------------|----------|
| 1 | John | Doe | john.doe@rambo.vid | 123456 |
| 2 | Jules | Ferry | jules.ferry@politician.old | 456789 |

members

| member_id | user_id | phone_number | country |
|-----------|---------|--------------|---------|
| 1 | 1 | 654789159 | en_US |
| 2 | 2 | | fr_FR |

What is the best way (more efficient execution) in MySQL to modify the existing database ?

mysql

share|improve this question

asked Mar 26 at 17:10

J.BizMaiJ.BizMai

84422 gold badges88 silver badges2222 bronze badges

| member_id | first_name | last_name | email | password | phone_number | country |
|-----------|------------|-----------|----------------------------|----------|--------------|---------|
| 1 | John | Doe | john.doe@rambo.vid | 123456 | 654789159 | en_US |
| 2 | Jules | Ferry | jules.ferry@politician.old | 456789 | | fr_FR |

| user_id | first_name | last_name | email | password |
|---------|------------|-----------|----------------------------|----------|
| 1 | John | Doe | john.doe@rambo.vid | 123456 |
| 2 | Jules | Ferry | jules.ferry@politician.old | 456789 |

| member_id | user_id | phone_number | country |
|-----------|---------|--------------|---------|
| 1 | 1 | 654789159 | en_US |
| 2 | 2 | | fr_FR |

share|improve this question

asked Mar 26 at 17:10

J.BizMaiJ.BizMai

84422 gold badges88 silver badges2222 bronze badges

share|improve this question

asked Mar 26 at 17:10

J.BizMaiJ.BizMai

84422 gold badges88 silver badges2222 bronze badges

asked Mar 26 at 17:10

J.BizMaiJ.BizMai

84422 gold badges88 silver badges2222 bronze badges

asked Mar 26 at 17:10

J.BizMaiJ.BizMai

84422 gold badges88 silver badges2222 bronze badges

1 Answer
1

active

oldest

votes

0

Here is what I am thinking:

INSERT INTO users ( user_id, member_id, first_name, last_name, email, password)
 SELECT <user_id>, -- Wherever this needs to come from or remove if its generated.
 member_id, first_name, last_name, email, password
 FROM members;

Followed by:

UPDATE members m INNER JOIN users u
 ON m.member_id = u.member_id 
 SET m.user_id = u.user_id

I have added the member_id field in the users table which you don't have. If you don't have the option to do that then you can use the other identifiers to join the table.

share|improve this answer

answered Mar 26 at 21:36

clinomaniacclinomaniac

2,17722 gold badges1313 silver badges2121 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Yes, it seems to be the best way but the user_id is auto_increment and I would like to get it for each row record to save it in users table without adding member_id in this table. unfortunately, I think I will need php code to do a loop to separate each record row.
  
  – J.BizMai
  Mar 27 at 22:56
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  No. You shouldn't have to loop as long as you can match the original data and the new data. Some common keys and you can update using the new table.
  
  – clinomaniac
  Mar 28 at 20:57
  

  
  
  
  

add a comment | 

Your Answer

StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);



);

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55362710%2fbest-mysql-way-to-move-data-from-table-to-another-one-with-junction%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

0

Here is what I am thinking:

INSERT INTO users ( user_id, member_id, first_name, last_name, email, password)
 SELECT <user_id>, -- Wherever this needs to come from or remove if its generated.
 member_id, first_name, last_name, email, password
 FROM members;

Followed by:

UPDATE members m INNER JOIN users u
 ON m.member_id = u.member_id 
 SET m.user_id = u.user_id

I have added the member_id field in the users table which you don't have. If you don't have the option to do that then you can use the other identifiers to join the table.

share|improve this answer

answered Mar 26 at 21:36

clinomaniacclinomaniac

2,17722 gold badges1313 silver badges2121 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Yes, it seems to be the best way but the user_id is auto_increment and I would like to get it for each row record to save it in users table without adding member_id in this table. unfortunately, I think I will need php code to do a loop to separate each record row.
  
  – J.BizMai
  Mar 27 at 22:56
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  No. You shouldn't have to loop as long as you can match the original data and the new data. Some common keys and you can update using the new table.
  
  – clinomaniac
  Mar 28 at 20:57
  

  
  
  
  

add a comment | 

0

Here is what I am thinking:

INSERT INTO users ( user_id, member_id, first_name, last_name, email, password)
 SELECT <user_id>, -- Wherever this needs to come from or remove if its generated.
 member_id, first_name, last_name, email, password
 FROM members;

Followed by:

UPDATE members m INNER JOIN users u
 ON m.member_id = u.member_id 
 SET m.user_id = u.user_id

I have added the member_id field in the users table which you don't have. If you don't have the option to do that then you can use the other identifiers to join the table.

share|improve this answer

answered Mar 26 at 21:36

clinomaniacclinomaniac

2,17722 gold badges1313 silver badges2121 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Yes, it seems to be the best way but the user_id is auto_increment and I would like to get it for each row record to save it in users table without adding member_id in this table. unfortunately, I think I will need php code to do a loop to separate each record row.
  
  – J.BizMai
  Mar 27 at 22:56
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  No. You shouldn't have to loop as long as you can match the original data and the new data. Some common keys and you can update using the new table.
  
  – clinomaniac
  Mar 28 at 20:57
  

  
  
  
  

add a comment | 

Here is what I am thinking:

INSERT INTO users ( user_id, member_id, first_name, last_name, email, password)
 SELECT <user_id>, -- Wherever this needs to come from or remove if its generated.
 member_id, first_name, last_name, email, password
 FROM members;

Followed by:

UPDATE members m INNER JOIN users u
 ON m.member_id = u.member_id 
 SET m.user_id = u.user_id

I have added the member_id field in the users table which you don't have. If you don't have the option to do that then you can use the other identifiers to join the table.

share|improve this answer

answered Mar 26 at 21:36

clinomaniacclinomaniac

2,17722 gold badges1313 silver badges2121 bronze badges

INSERT INTO users ( user_id, member_id, first_name, last_name, email, password)
 SELECT <user_id>, -- Wherever this needs to come from or remove if its generated.
 member_id, first_name, last_name, email, password
 FROM members;

UPDATE members m INNER JOIN users u
 ON m.member_id = u.member_id 
 SET m.user_id = u.user_id

share|improve this answer

answered Mar 26 at 21:36

clinomaniacclinomaniac

2,17722 gold badges1313 silver badges2121 bronze badges

answered Mar 26 at 21:36

clinomaniacclinomaniac

2,17722 gold badges1313 silver badges2121 bronze badges

answered Mar 26 at 21:36

clinomaniacclinomaniac

2,17722 gold badges1313 silver badges2121 bronze badges