SQL Server: How to select existing IDs and insert them to the new ID field
I have two tables Category and Movie and I have a mapping table MovieCategory (a movie can have one or multiple categories).
I then noticed that in my table Movie, there's not one movie with multiple categories.
So I want to delete the MovieCategory mapping table. But first, to accomplish this, I created a new column IDCategory in my table Movie which references the Category (a movie can now have only one single category).
And now, my new IDCategory column is null for all my Movie entries, I want to select all the existing Category entries in my mapping table MovieCategory and then insert the selected IDCategory to my new IDCategory column in the Movie table.
How can I accomplish this?
Here are my columns:
MovieCategory
---------------------
IDMovie IDCategory
----------------------
Category table has IDCategory and Movie table has IDMovie and the new IDCategory column
sql
sql-server tsql edited Nov 14 '18 at 18:47
Rahul Neekhra
6001627
asked Nov 14 '18 at 17:25
user2426691user2426691
4717
add a comment |
I have two tables Category and Movie and I have a mapping table MovieCategory (a movie can have one or multiple categories).
I then noticed that in my table Movie, there's not one movie with multiple categories.
So I want to delete the MovieCategory mapping table. But first, to accomplish this, I created a new column IDCategory in my table Movie which references the Category (a movie can now have only one single category).
And now, my new IDCategory column is null for all my Movie entries, I want to select all the existing Category entries in my mapping table MovieCategory and then insert the selected IDCategory to my new IDCategory column in the Movie table.
How can I accomplish this?
Here are my columns:
MovieCategory
---------------------
IDMovie IDCategory
----------------------
Category table has IDCategory and Movie table has IDMovie and the new IDCategory column
sql
sql-server tsql edited Nov 14 '18 at 18:47
Rahul Neekhra
6001627
asked Nov 14 '18 at 17:25
user2426691user2426691
4717
I don't think you have Fields there, you have Columns instead, BTW I read your question but I can't understand what are you trying to do.
– Sami
Nov 14 '18 at 17:29
@Sami Sorry my brother Sami, I'll edit my question
– user2426691
Nov 14 '18 at 17:36
add a comment |
I have two tables Category and Movie and I have a mapping table MovieCategory (a movie can have one or multiple categories).
I then noticed that in my table Movie, there's not one movie with multiple categories.
So I want to delete the MovieCategory mapping table. But first, to accomplish this, I created a new column IDCategory in my table Movie which references the Category (a movie can now have only one single category).
And now, my new IDCategory column is null for all my Movie entries, I want to select all the existing Category entries in my mapping table MovieCategory and then insert the selected IDCategory to my new IDCategory column in the Movie table.
How can I accomplish this?
Here are my columns:
MovieCategory
---------------------
IDMovie IDCategory
----------------------
Category table has IDCategory and Movie table has IDMovie and the new IDCategory column
sql
sql-server tsql edited Nov 14 '18 at 18:47
Rahul Neekhra
6001627
asked Nov 14 '18 at 17:25
user2426691user2426691
4717
I have two tables Category and Movie and I have a mapping table MovieCategory (a movie can have one or multiple categories).
I then noticed that in my table Movie, there's not one movie with multiple categories.
So I want to delete the MovieCategory mapping table. But first, to accomplish this, I created a new column IDCategory in my table Movie which references the Category (a movie can now have only one single category).
And now, my new IDCategory column is null for all my Movie entries, I want to select all the existing Category entries in my mapping table MovieCategory and then insert the selected IDCategory to my new IDCategory column in the Movie table.
How can I accomplish this?
Here are my columns:
MovieCategory
---------------------
IDMovie IDCategory
----------------------
Category table has IDCategory and Movie table has IDMovie and the new IDCategory column
sql
sql-server tsql sql
sql-server tsql edited Nov 14 '18 at 18:47
Rahul Neekhra
6001627
asked Nov 14 '18 at 17:25
user2426691user2426691
4717
edited Nov 14 '18 at 18:47
Rahul Neekhra
6001627
asked Nov 14 '18 at 17:25
user2426691user2426691
4717
edited Nov 14 '18 at 18:47
Rahul Neekhra
6001627
edited Nov 14 '18 at 18:47
Rahul Neekhra
6001627
edited Nov 14 '18 at 18:47
Rahul Neekhra
6001627
6001627
asked Nov 14 '18 at 17:25
user2426691user2426691
4717
asked Nov 14 '18 at 17:25
user2426691user2426691
4717
asked Nov 14 '18 at 17:25
user2426691user2426691
4717
4717
I don't think you have Fields there, you have Columns instead, BTW I read your question but I can't understand what are you trying to do.
– Sami
Nov 14 '18 at 17:29
@Sami Sorry my brother Sami, I'll edit my question
– user2426691
Nov 14 '18 at 17:36
add a comment |
I don't think you have Fields there, you have Columns instead, BTW I read your question but I can't understand what are you trying to do.
– Sami
Nov 14 '18 at 17:29
@Sami Sorry my brother Sami, I'll edit my question
– user2426691
Nov 14 '18 at 17:36
I don't think you have Fields there, you have Columns instead, BTW I read your question but I can't understand what are you trying to do.
– Sami
Nov 14 '18 at 17:29
I don't think you have Fields there, you have Columns instead, BTW I read your question but I can't understand what are you trying to do.
– Sami
Nov 14 '18 at 17:29
@Sami Sorry my brother Sami, I'll edit my question
– user2426691
Nov 14 '18 at 17:36
@Sami Sorry my brother Sami, I'll edit my question
– user2426691
Nov 14 '18 at 17:36
add a comment |
1 Answer
1
active
oldest
votes
This is just a simple UPDATE statement:
UPDATE M
SET IDCategory = MC.IDCategory
FROM Movie M
JOIN MovieCategory MC ON MC.IDMovie = M.ID;
This is one of the basics of SQL; I suggest having a read up on the UPDATE syntax: SQL Update Statement & UPDATE (Transact-SQL)
answered Nov 14 '18 at 17:31
LarnuLarnu
18.2k51731
Larnu Please not w3schools
– Sami
Nov 14 '18 at 17:32
@sami please spell my name correctly. :) And W3CSchools is fine for an introduction; which is what the OP appears to need.
– Larnu
Nov 14 '18 at 17:33
See? I edit it, and here is a link to why not w3schools
– Sami
Nov 14 '18 at 17:33
@Larnu. . . You should use some other short name nearestLarnu.
– Yogesh Sharma
Nov 14 '18 at 17:36
nearest Larnu @YogeshSharma ?
– Larnu
Nov 14 '18 at 17:37
|
show 3 more comments
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
});
}
});
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
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53305696%2fsql-server-how-to-select-existing-ids-and-insert-them-to-the-new-id-field%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
This is just a simple UPDATE statement:
UPDATE M
SET IDCategory = MC.IDCategory
FROM Movie M
JOIN MovieCategory MC ON MC.IDMovie = M.ID;
This is one of the basics of SQL; I suggest having a read up on the UPDATE syntax: SQL Update Statement & UPDATE (Transact-SQL)
answered Nov 14 '18 at 17:31
LarnuLarnu
18.2k51731
Larnu Please not w3schools
– Sami
Nov 14 '18 at 17:32
@sami please spell my name correctly. :) And W3CSchools is fine for an introduction; which is what the OP appears to need.
– Larnu
Nov 14 '18 at 17:33
See? I edit it, and here is a link to why not w3schools
– Sami
Nov 14 '18 at 17:33
@Larnu. . . You should use some other short name nearestLarnu.
– Yogesh Sharma
Nov 14 '18 at 17:36
nearest Larnu @YogeshSharma ?
– Larnu
Nov 14 '18 at 17:37
|
show 3 more comments
This is just a simple UPDATE statement:
UPDATE M
SET IDCategory = MC.IDCategory
FROM Movie M
JOIN MovieCategory MC ON MC.IDMovie = M.ID;
This is one of the basics of SQL; I suggest having a read up on the UPDATE syntax: SQL Update Statement & UPDATE (Transact-SQL)
answered Nov 14 '18 at 17:31
LarnuLarnu
18.2k51731
Larnu Please not w3schools
– Sami
Nov 14 '18 at 17:32
@sami please spell my name correctly. :) And W3CSchools is fine for an introduction; which is what the OP appears to need.
– Larnu
Nov 14 '18 at 17:33
See? I edit it, and here is a link to why not w3schools
– Sami
Nov 14 '18 at 17:33
@Larnu. . . You should use some other short name nearestLarnu.
– Yogesh Sharma
Nov 14 '18 at 17:36
nearest Larnu @YogeshSharma ?
– Larnu
Nov 14 '18 at 17:37
|
show 3 more comments
This is just a simple UPDATE statement:
UPDATE M
SET IDCategory = MC.IDCategory
FROM Movie M
JOIN MovieCategory MC ON MC.IDMovie = M.ID;
This is one of the basics of SQL; I suggest having a read up on the UPDATE syntax: SQL Update Statement & UPDATE (Transact-SQL)
answered Nov 14 '18 at 17:31
LarnuLarnu
18.2k51731
This is just a simple UPDATE statement:
UPDATE M
SET IDCategory = MC.IDCategory
FROM Movie M
JOIN MovieCategory MC ON MC.IDMovie = M.ID;
This is one of the basics of SQL; I suggest having a read up on the UPDATE syntax: SQL Update Statement & UPDATE (Transact-SQL)
answered Nov 14 '18 at 17:31
LarnuLarnu
18.2k51731
answered Nov 14 '18 at 17:31
LarnuLarnu
18.2k51731
answered Nov 14 '18 at 17:31
LarnuLarnu
18.2k51731
answered Nov 14 '18 at 17:31
LarnuLarnu
18.2k51731
18.2k51731
Larnu Please not w3schools
– Sami
Nov 14 '18 at 17:32
@sami please spell my name correctly. :) And W3CSchools is fine for an introduction; which is what the OP appears to need.
– Larnu
Nov 14 '18 at 17:33
See? I edit it, and here is a link to why not w3schools
– Sami
Nov 14 '18 at 17:33
@Larnu. . . You should use some other short name nearestLarnu.
– Yogesh Sharma
Nov 14 '18 at 17:36
nearest Larnu @YogeshSharma ?
– Larnu
Nov 14 '18 at 17:37
|
show 3 more comments
Larnu Please not w3schools
– Sami
Nov 14 '18 at 17:32
@sami please spell my name correctly. :) And W3CSchools is fine for an introduction; which is what the OP appears to need.
– Larnu
Nov 14 '18 at 17:33
See? I edit it, and here is a link to why not w3schools
– Sami
Nov 14 '18 at 17:33
@Larnu. . . You should use some other short name nearestLarnu.
– Yogesh Sharma
Nov 14 '18 at 17:36
nearest Larnu @YogeshSharma ?
– Larnu
Nov 14 '18 at 17:37
Larnu Please not w3schools
– Sami
Nov 14 '18 at 17:32
Larnu Please not w3schools
– Sami
Nov 14 '18 at 17:32
@sami please spell my name correctly. :) And W3CSchools is fine for an introduction; which is what the OP appears to need.
– Larnu
Nov 14 '18 at 17:33
@sami please spell my name correctly. :) And W3CSchools is fine for an introduction; which is what the OP appears to need.
– Larnu
Nov 14 '18 at 17:33
See? I edit it, and here is a link to why not w3schools
– Sami
Nov 14 '18 at 17:33
See? I edit it, and here is a link to why not w3schools
– Sami
Nov 14 '18 at 17:33
@Larnu. . . You should use some other short name nearest
Larnu.– Yogesh Sharma
Nov 14 '18 at 17:36
@Larnu. . . You should use some other short name nearest
Larnu.– Yogesh Sharma
Nov 14 '18 at 17:36
nearest Larnu @YogeshSharma ?
– Larnu
Nov 14 '18 at 17:37
nearest Larnu @YogeshSharma ?
– Larnu
Nov 14 '18 at 17:37
|
show 3 more comments
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
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
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53305696%2fsql-server-how-to-select-existing-ids-and-insert-them-to-the-new-id-field%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
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
Required, but never shown
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
Required, but never shown
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
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
I don't think you have Fields there, you have Columns instead, BTW I read your question but I can't understand what are you trying to do.
– Sami
Nov 14 '18 at 17:29
@Sami Sorry my brother Sami, I'll edit my question
– user2426691
Nov 14 '18 at 17:36