Join two tables, Inserting values into second table
I tried searching here for a similar solution but didn't see one so I was wondering what is the best way to accomplish the following.
I have a table
Table1 t1
t1.c1 ..
1
2
3
4
5
6
values
and have another table like this
Table2 t2
t2.c1 t2.c2 t2.c3 ..
1 v1 v2
2 v3 v4
3 v5 v6
5 v7 v8
7 v9 v10
I need to insert/update table 2
where it has t2.c1 = 7
so the result would look like this
t2.c1 t2.c2 t2.c3 ..
1 v1 v2
2 v3 v4
3 v5 v6
5 v7 v8
1 v9 v10
2 v9 v10
3 v9 v10
4 v9 v10
5 v9 v10
6 v9 v10
I was trying to first see if there was in the column t2.c1 the value 7 .If existed them Insert the new rows . After the insert, delete the row with the value 7, but I really don´t know how to do so..
Can anyone give some ideias how to solve this?
sql sql-server
add a comment |
I tried searching here for a similar solution but didn't see one so I was wondering what is the best way to accomplish the following.
I have a table
Table1 t1
t1.c1 ..
1
2
3
4
5
6
values
and have another table like this
Table2 t2
t2.c1 t2.c2 t2.c3 ..
1 v1 v2
2 v3 v4
3 v5 v6
5 v7 v8
7 v9 v10
I need to insert/update table 2
where it has t2.c1 = 7
so the result would look like this
t2.c1 t2.c2 t2.c3 ..
1 v1 v2
2 v3 v4
3 v5 v6
5 v7 v8
1 v9 v10
2 v9 v10
3 v9 v10
4 v9 v10
5 v9 v10
6 v9 v10
I was trying to first see if there was in the column t2.c1 the value 7 .If existed them Insert the new rows . After the insert, delete the row with the value 7, but I really don´t know how to do so..
Can anyone give some ideias how to solve this?
sql sql-server
add a comment |
I tried searching here for a similar solution but didn't see one so I was wondering what is the best way to accomplish the following.
I have a table
Table1 t1
t1.c1 ..
1
2
3
4
5
6
values
and have another table like this
Table2 t2
t2.c1 t2.c2 t2.c3 ..
1 v1 v2
2 v3 v4
3 v5 v6
5 v7 v8
7 v9 v10
I need to insert/update table 2
where it has t2.c1 = 7
so the result would look like this
t2.c1 t2.c2 t2.c3 ..
1 v1 v2
2 v3 v4
3 v5 v6
5 v7 v8
1 v9 v10
2 v9 v10
3 v9 v10
4 v9 v10
5 v9 v10
6 v9 v10
I was trying to first see if there was in the column t2.c1 the value 7 .If existed them Insert the new rows . After the insert, delete the row with the value 7, but I really don´t know how to do so..
Can anyone give some ideias how to solve this?
sql sql-server
I tried searching here for a similar solution but didn't see one so I was wondering what is the best way to accomplish the following.
I have a table
Table1 t1
t1.c1 ..
1
2
3
4
5
6
values
and have another table like this
Table2 t2
t2.c1 t2.c2 t2.c3 ..
1 v1 v2
2 v3 v4
3 v5 v6
5 v7 v8
7 v9 v10
I need to insert/update table 2
where it has t2.c1 = 7
so the result would look like this
t2.c1 t2.c2 t2.c3 ..
1 v1 v2
2 v3 v4
3 v5 v6
5 v7 v8
1 v9 v10
2 v9 v10
3 v9 v10
4 v9 v10
5 v9 v10
6 v9 v10
I was trying to first see if there was in the column t2.c1 the value 7 .If existed them Insert the new rows . After the insert, delete the row with the value 7, but I really don´t know how to do so..
Can anyone give some ideias how to solve this?
sql sql-server
sql sql-server
edited Nov 13 '18 at 23:04
abatishchev
69.4k70263393
69.4k70263393
asked Nov 13 '18 at 23:02
hi itsmehi itsme
1661517
1661517
add a comment |
add a comment |
2 Answers
2
active
oldest
votes
It looks like you need a cartesian product of tables t2
and t1
for all records where t2.c1 = 7
, e.g.:
INSERT INTO Table2
SELECT t1.c1, t2.c2, t2.c3
FROM Table1 t1, Table2 t2
WHERE t2.c1 = 7
Followed by a deletion of those records with t2.c1 = 7
, e.g.:
DELETE FROM Table2 t2 WHERE t2.c1 = 7
add a comment |
First delete the c1=7 then populate it data from table1
DELETE FROM Table2
WHERE c1 = 7;
INSERT INTO Table2 (c1,c2,c3)
SELECT t1.c1,t1.c2,t1.c3
FROM Table1 as t1;
FIDDLE
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
);
);
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%2f53290804%2fjoin-two-tables-inserting-values-into-second-table%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
It looks like you need a cartesian product of tables t2
and t1
for all records where t2.c1 = 7
, e.g.:
INSERT INTO Table2
SELECT t1.c1, t2.c2, t2.c3
FROM Table1 t1, Table2 t2
WHERE t2.c1 = 7
Followed by a deletion of those records with t2.c1 = 7
, e.g.:
DELETE FROM Table2 t2 WHERE t2.c1 = 7
add a comment |
It looks like you need a cartesian product of tables t2
and t1
for all records where t2.c1 = 7
, e.g.:
INSERT INTO Table2
SELECT t1.c1, t2.c2, t2.c3
FROM Table1 t1, Table2 t2
WHERE t2.c1 = 7
Followed by a deletion of those records with t2.c1 = 7
, e.g.:
DELETE FROM Table2 t2 WHERE t2.c1 = 7
add a comment |
It looks like you need a cartesian product of tables t2
and t1
for all records where t2.c1 = 7
, e.g.:
INSERT INTO Table2
SELECT t1.c1, t2.c2, t2.c3
FROM Table1 t1, Table2 t2
WHERE t2.c1 = 7
Followed by a deletion of those records with t2.c1 = 7
, e.g.:
DELETE FROM Table2 t2 WHERE t2.c1 = 7
It looks like you need a cartesian product of tables t2
and t1
for all records where t2.c1 = 7
, e.g.:
INSERT INTO Table2
SELECT t1.c1, t2.c2, t2.c3
FROM Table1 t1, Table2 t2
WHERE t2.c1 = 7
Followed by a deletion of those records with t2.c1 = 7
, e.g.:
DELETE FROM Table2 t2 WHERE t2.c1 = 7
answered Nov 13 '18 at 23:24
Lee MacLee Mac
4,05631440
4,05631440
add a comment |
add a comment |
First delete the c1=7 then populate it data from table1
DELETE FROM Table2
WHERE c1 = 7;
INSERT INTO Table2 (c1,c2,c3)
SELECT t1.c1,t1.c2,t1.c3
FROM Table1 as t1;
FIDDLE
add a comment |
First delete the c1=7 then populate it data from table1
DELETE FROM Table2
WHERE c1 = 7;
INSERT INTO Table2 (c1,c2,c3)
SELECT t1.c1,t1.c2,t1.c3
FROM Table1 as t1;
FIDDLE
add a comment |
First delete the c1=7 then populate it data from table1
DELETE FROM Table2
WHERE c1 = 7;
INSERT INTO Table2 (c1,c2,c3)
SELECT t1.c1,t1.c2,t1.c3
FROM Table1 as t1;
FIDDLE
First delete the c1=7 then populate it data from table1
DELETE FROM Table2
WHERE c1 = 7;
INSERT INTO Table2 (c1,c2,c3)
SELECT t1.c1,t1.c2,t1.c3
FROM Table1 as t1;
FIDDLE
answered Nov 13 '18 at 23:31
comphoniacomphonia
43828
43828
add a comment |
add a comment |
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%2f53290804%2fjoin-two-tables-inserting-values-into-second-table%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