How to insert comparison records results between 2 tables into another table?
up vote
1
down vote
favorite
status_tb
+----+----------+-------------+----------+
| id | status | description | state_id |
+----+----------+-------------+----------+
| 1 | new | north | 1 |
| 2 | assign | south | 2 |
| 3 |Postponed | east | 2 |
| 4 | Fixed | west | 3 |
| 35 | Test | South-test | 4 |
+----+----------+-------------+----------+
status_backup_tb
+----------+----+----------+-------------+----------+
|backup_id | id | status | description | state_id |
+----------+----+----------+-------------+----------+
| 1 | 1 |new | north | 1 |
| 2 | 2 |assign | south | 2 |
| 3 | 3 |Postponed | east | 2 |
| 4 | 4 | Fixed | west | 3 |
| 7 | 35| Rejected | Testing | 4 |
+----------+----+----------+-------------+----------+
Once I get my input result from the resulting mysql, I need to insert those results to another table(audit_status_tb) like below...
How do I achieve it?
Here the sql to get my required records
(SELECT
s.id,
'status' AS Column_changed,
s.status AS Old_value,
b.status AS New_value
FROM status_tb AS s
JOIN status_backup_tb AS b
ON b.id = s.id AND
b.status <> s.status)
UNION ALL
(SELECT
s.id,
'description' AS Column_changed,
s.description AS Old_value,
b.description AS New_value
FROM status_tb AS s
JOIN status_backup_tb AS b
ON b.id = s.id AND
b.description <> s.description)
UNION ALL
(SELECT
s.id,
'state_id' AS Column_changed,
s.state_id AS Old_value,
b.state_id AS New_value
FROM status_tb AS s
JOIN status_backup_tb AS b
ON b.id = s.id AND
b.state_id <> s.state_id)
audit_status_tb
|new_id | id |Column_changed| Old_value |New_value |
+-------+----+--------------+-------------+----------+
|1 | 35 | status | Test | Rejected |
|2 | 35 |description | South-test | Testing |
+-------+----+--------------+-------------+----------+
Im not sure about what kind of insert-select? mysql I should used to retrieve those values and input them in the above format...
mysql database insert compare
asked Nov 21 at 12:31
Emanula Sohn
215
add a comment |
up vote
1
down vote
favorite
status_tb
+----+----------+-------------+----------+
| id | status | description | state_id |
+----+----------+-------------+----------+
| 1 | new | north | 1 |
| 2 | assign | south | 2 |
| 3 |Postponed | east | 2 |
| 4 | Fixed | west | 3 |
| 35 | Test | South-test | 4 |
+----+----------+-------------+----------+
status_backup_tb
+----------+----+----------+-------------+----------+
|backup_id | id | status | description | state_id |
+----------+----+----------+-------------+----------+
| 1 | 1 |new | north | 1 |
| 2 | 2 |assign | south | 2 |
| 3 | 3 |Postponed | east | 2 |
| 4 | 4 | Fixed | west | 3 |
| 7 | 35| Rejected | Testing | 4 |
+----------+----+----------+-------------+----------+
Once I get my input result from the resulting mysql, I need to insert those results to another table(audit_status_tb) like below...
How do I achieve it?
Here the sql to get my required records
(SELECT
s.id,
'status' AS Column_changed,
s.status AS Old_value,
b.status AS New_value
FROM status_tb AS s
JOIN status_backup_tb AS b
ON b.id = s.id AND
b.status <> s.status)
UNION ALL
(SELECT
s.id,
'description' AS Column_changed,
s.description AS Old_value,
b.description AS New_value
FROM status_tb AS s
JOIN status_backup_tb AS b
ON b.id = s.id AND
b.description <> s.description)
UNION ALL
(SELECT
s.id,
'state_id' AS Column_changed,
s.state_id AS Old_value,
b.state_id AS New_value
FROM status_tb AS s
JOIN status_backup_tb AS b
ON b.id = s.id AND
b.state_id <> s.state_id)
audit_status_tb
|new_id | id |Column_changed| Old_value |New_value |
+-------+----+--------------+-------------+----------+
|1 | 35 | status | Test | Rejected |
|2 | 35 |description | South-test | Testing |
+-------+----+--------------+-------------+----------+
Im not sure about what kind of insert-select? mysql I should used to retrieve those values and input them in the above format...
mysql database insert compare
asked Nov 21 at 12:31
Emanula Sohn
215
Is new_id a primary key auto increment in audit_status_tb ?
– Madhur Bhaiya
Nov 21 at 12:35
yes, I just need to insert as from id
– Emanula Sohn
Nov 21 at 12:36
add a comment |
up vote
1
down vote
favorite
up vote
1
down vote
favorite
status_tb
+----+----------+-------------+----------+
| id | status | description | state_id |
+----+----------+-------------+----------+
| 1 | new | north | 1 |
| 2 | assign | south | 2 |
| 3 |Postponed | east | 2 |
| 4 | Fixed | west | 3 |
| 35 | Test | South-test | 4 |
+----+----------+-------------+----------+
status_backup_tb
+----------+----+----------+-------------+----------+
|backup_id | id | status | description | state_id |
+----------+----+----------+-------------+----------+
| 1 | 1 |new | north | 1 |
| 2 | 2 |assign | south | 2 |
| 3 | 3 |Postponed | east | 2 |
| 4 | 4 | Fixed | west | 3 |
| 7 | 35| Rejected | Testing | 4 |
+----------+----+----------+-------------+----------+
Once I get my input result from the resulting mysql, I need to insert those results to another table(audit_status_tb) like below...
How do I achieve it?
Here the sql to get my required records
(SELECT
s.id,
'status' AS Column_changed,
s.status AS Old_value,
b.status AS New_value
FROM status_tb AS s
JOIN status_backup_tb AS b
ON b.id = s.id AND
b.status <> s.status)
UNION ALL
(SELECT
s.id,
'description' AS Column_changed,
s.description AS Old_value,
b.description AS New_value
FROM status_tb AS s
JOIN status_backup_tb AS b
ON b.id = s.id AND
b.description <> s.description)
UNION ALL
(SELECT
s.id,
'state_id' AS Column_changed,
s.state_id AS Old_value,
b.state_id AS New_value
FROM status_tb AS s
JOIN status_backup_tb AS b
ON b.id = s.id AND
b.state_id <> s.state_id)
audit_status_tb
|new_id | id |Column_changed| Old_value |New_value |
+-------+----+--------------+-------------+----------+
|1 | 35 | status | Test | Rejected |
|2 | 35 |description | South-test | Testing |
+-------+----+--------------+-------------+----------+
Im not sure about what kind of insert-select? mysql I should used to retrieve those values and input them in the above format...
mysql database insert compare
asked Nov 21 at 12:31
Emanula Sohn
215
status_tb
+----+----------+-------------+----------+
| id | status | description | state_id |
+----+----------+-------------+----------+
| 1 | new | north | 1 |
| 2 | assign | south | 2 |
| 3 |Postponed | east | 2 |
| 4 | Fixed | west | 3 |
| 35 | Test | South-test | 4 |
+----+----------+-------------+----------+
status_backup_tb
+----------+----+----------+-------------+----------+
|backup_id | id | status | description | state_id |
+----------+----+----------+-------------+----------+
| 1 | 1 |new | north | 1 |
| 2 | 2 |assign | south | 2 |
| 3 | 3 |Postponed | east | 2 |
| 4 | 4 | Fixed | west | 3 |
| 7 | 35| Rejected | Testing | 4 |
+----------+----+----------+-------------+----------+
Once I get my input result from the resulting mysql, I need to insert those results to another table(audit_status_tb) like below...
How do I achieve it?
Here the sql to get my required records
(SELECT
s.id,
'status' AS Column_changed,
s.status AS Old_value,
b.status AS New_value
FROM status_tb AS s
JOIN status_backup_tb AS b
ON b.id = s.id AND
b.status <> s.status)
UNION ALL
(SELECT
s.id,
'description' AS Column_changed,
s.description AS Old_value,
b.description AS New_value
FROM status_tb AS s
JOIN status_backup_tb AS b
ON b.id = s.id AND
b.description <> s.description)
UNION ALL
(SELECT
s.id,
'state_id' AS Column_changed,
s.state_id AS Old_value,
b.state_id AS New_value
FROM status_tb AS s
JOIN status_backup_tb AS b
ON b.id = s.id AND
b.state_id <> s.state_id)
audit_status_tb
|new_id | id |Column_changed| Old_value |New_value |
+-------+----+--------------+-------------+----------+
|1 | 35 | status | Test | Rejected |
|2 | 35 |description | South-test | Testing |
+-------+----+--------------+-------------+----------+
Im not sure about what kind of insert-select? mysql I should used to retrieve those values and input them in the above format...
mysql database insert compare
mysql database insert compare
asked Nov 21 at 12:31
Emanula Sohn
215
asked Nov 21 at 12:31
Emanula Sohn
215
asked Nov 21 at 12:31
Emanula Sohn
215
asked Nov 21 at 12:31
Emanula Sohn
215
asked Nov 21 at 12:31
Emanula Sohn
215
215
Is new_id a primary key auto increment in audit_status_tb ?
– Madhur Bhaiya
Nov 21 at 12:35
yes, I just need to insert as from id
– Emanula Sohn
Nov 21 at 12:36
add a comment |
Is new_id a primary key auto increment in audit_status_tb ?
– Madhur Bhaiya
Nov 21 at 12:35
yes, I just need to insert as from id
– Emanula Sohn
Nov 21 at 12:36
Is new_id a primary key auto increment in audit_status_tb ?
– Madhur Bhaiya
Nov 21 at 12:35
Is new_id a primary key auto increment in audit_status_tb ?
– Madhur Bhaiya
Nov 21 at 12:35
yes, I just need to insert as from id
– Emanula Sohn
Nov 21 at 12:36
yes, I just need to insert as from id
– Emanula Sohn
Nov 21 at 12:36
add a comment |
1 Answer
1
active
oldest
votes
up vote
1
down vote
accepted
You can simply use Insert Into .. Select statement:
INSERT INTO audit_status_tb (id, Column_changed, Old_value, New_value)
SELECT
s.id,
'status' AS Column_changed,
s.status AS Old_value,
b.status AS New_value
FROM status_tb AS s
JOIN status_backup_tb AS b
ON b.id = s.id AND
b.status <> s.status
UNION ALL
SELECT
s.id,
'description' AS Column_changed,
s.description AS Old_value,
b.description AS New_value
FROM status_tb AS s
JOIN status_backup_tb AS b
ON b.id = s.id AND
b.description <> s.description
UNION ALL
SELECT
s.id,
'state_id' AS Column_changed,
s.state_id AS Old_value,
b.state_id AS New_value
FROM status_tb AS s
JOIN status_backup_tb AS b
ON b.id = s.id AND
b.state_id <> s.state_id
answered Nov 21 at 12:36
Madhur Bhaiya
18.8k62236
thanks that what I try yesterday but wasnt sure where to properly put the select statement for the insert
– Emanula Sohn
Nov 21 at 12:39
@EmanulaSohn my query should work. Let me know if you are facing any syntax errors ?
– Madhur Bhaiya
Nov 21 at 12:43
Error, Im getting duplicate if I re-execute the query. I should not have any duplicate results in theaudit_status_tbtable.Help pls By duplicate I mean, sameid, state_id, status , descriptionrows in some cases...
– Emanula Sohn
Nov 21 at 17:59
@EmanulaSohn it will obviously reenter the same data. One way is to put a composite unique constraint on(id, Column_changed, old_value, new_value)
– Madhur Bhaiya
Nov 21 at 18:01
how do I add this composite unique constraint? I try addingWHERE s.id NOT IN (SELECT id FROM audit_status_tb)at the end of each select statement and its seems to work...What do you think?
– Emanula Sohn
Nov 21 at 18:12
|
show 4 more comments
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
1
down vote
accepted
You can simply use Insert Into .. Select statement:
INSERT INTO audit_status_tb (id, Column_changed, Old_value, New_value)
SELECT
s.id,
'status' AS Column_changed,
s.status AS Old_value,
b.status AS New_value
FROM status_tb AS s
JOIN status_backup_tb AS b
ON b.id = s.id AND
b.status <> s.status
UNION ALL
SELECT
s.id,
'description' AS Column_changed,
s.description AS Old_value,
b.description AS New_value
FROM status_tb AS s
JOIN status_backup_tb AS b
ON b.id = s.id AND
b.description <> s.description
UNION ALL
SELECT
s.id,
'state_id' AS Column_changed,
s.state_id AS Old_value,
b.state_id AS New_value
FROM status_tb AS s
JOIN status_backup_tb AS b
ON b.id = s.id AND
b.state_id <> s.state_id
answered Nov 21 at 12:36
Madhur Bhaiya
18.8k62236
thanks that what I try yesterday but wasnt sure where to properly put the select statement for the insert
– Emanula Sohn
Nov 21 at 12:39
@EmanulaSohn my query should work. Let me know if you are facing any syntax errors ?
– Madhur Bhaiya
Nov 21 at 12:43
Error, Im getting duplicate if I re-execute the query. I should not have any duplicate results in theaudit_status_tbtable.Help pls By duplicate I mean, sameid, state_id, status , descriptionrows in some cases...
– Emanula Sohn
Nov 21 at 17:59
@EmanulaSohn it will obviously reenter the same data. One way is to put a composite unique constraint on(id, Column_changed, old_value, new_value)
– Madhur Bhaiya
Nov 21 at 18:01
how do I add this composite unique constraint? I try addingWHERE s.id NOT IN (SELECT id FROM audit_status_tb)at the end of each select statement and its seems to work...What do you think?
– Emanula Sohn
Nov 21 at 18:12
|
show 4 more comments
up vote
1
down vote
accepted
You can simply use Insert Into .. Select statement:
INSERT INTO audit_status_tb (id, Column_changed, Old_value, New_value)
SELECT
s.id,
'status' AS Column_changed,
s.status AS Old_value,
b.status AS New_value
FROM status_tb AS s
JOIN status_backup_tb AS b
ON b.id = s.id AND
b.status <> s.status
UNION ALL
SELECT
s.id,
'description' AS Column_changed,
s.description AS Old_value,
b.description AS New_value
FROM status_tb AS s
JOIN status_backup_tb AS b
ON b.id = s.id AND
b.description <> s.description
UNION ALL
SELECT
s.id,
'state_id' AS Column_changed,
s.state_id AS Old_value,
b.state_id AS New_value
FROM status_tb AS s
JOIN status_backup_tb AS b
ON b.id = s.id AND
b.state_id <> s.state_id
answered Nov 21 at 12:36
Madhur Bhaiya
18.8k62236
thanks that what I try yesterday but wasnt sure where to properly put the select statement for the insert
– Emanula Sohn
Nov 21 at 12:39
@EmanulaSohn my query should work. Let me know if you are facing any syntax errors ?
– Madhur Bhaiya
Nov 21 at 12:43
Error, Im getting duplicate if I re-execute the query. I should not have any duplicate results in theaudit_status_tbtable.Help pls By duplicate I mean, sameid, state_id, status , descriptionrows in some cases...
– Emanula Sohn
Nov 21 at 17:59
@EmanulaSohn it will obviously reenter the same data. One way is to put a composite unique constraint on(id, Column_changed, old_value, new_value)
– Madhur Bhaiya
Nov 21 at 18:01
how do I add this composite unique constraint? I try addingWHERE s.id NOT IN (SELECT id FROM audit_status_tb)at the end of each select statement and its seems to work...What do you think?
– Emanula Sohn
Nov 21 at 18:12
|
show 4 more comments
up vote
1
down vote
accepted
up vote
1
down vote
accepted
You can simply use Insert Into .. Select statement:
INSERT INTO audit_status_tb (id, Column_changed, Old_value, New_value)
SELECT
s.id,
'status' AS Column_changed,
s.status AS Old_value,
b.status AS New_value
FROM status_tb AS s
JOIN status_backup_tb AS b
ON b.id = s.id AND
b.status <> s.status
UNION ALL
SELECT
s.id,
'description' AS Column_changed,
s.description AS Old_value,
b.description AS New_value
FROM status_tb AS s
JOIN status_backup_tb AS b
ON b.id = s.id AND
b.description <> s.description
UNION ALL
SELECT
s.id,
'state_id' AS Column_changed,
s.state_id AS Old_value,
b.state_id AS New_value
FROM status_tb AS s
JOIN status_backup_tb AS b
ON b.id = s.id AND
b.state_id <> s.state_id
answered Nov 21 at 12:36
Madhur Bhaiya
18.8k62236
You can simply use Insert Into .. Select statement:
INSERT INTO audit_status_tb (id, Column_changed, Old_value, New_value)
SELECT
s.id,
'status' AS Column_changed,
s.status AS Old_value,
b.status AS New_value
FROM status_tb AS s
JOIN status_backup_tb AS b
ON b.id = s.id AND
b.status <> s.status
UNION ALL
SELECT
s.id,
'description' AS Column_changed,
s.description AS Old_value,
b.description AS New_value
FROM status_tb AS s
JOIN status_backup_tb AS b
ON b.id = s.id AND
b.description <> s.description
UNION ALL
SELECT
s.id,
'state_id' AS Column_changed,
s.state_id AS Old_value,
b.state_id AS New_value
FROM status_tb AS s
JOIN status_backup_tb AS b
ON b.id = s.id AND
b.state_id <> s.state_id
answered Nov 21 at 12:36
Madhur Bhaiya
18.8k62236
answered Nov 21 at 12:36
Madhur Bhaiya
18.8k62236
answered Nov 21 at 12:36
Madhur Bhaiya
18.8k62236
answered Nov 21 at 12:36
Madhur Bhaiya
18.8k62236
18.8k62236
thanks that what I try yesterday but wasnt sure where to properly put the select statement for the insert
– Emanula Sohn
Nov 21 at 12:39
@EmanulaSohn my query should work. Let me know if you are facing any syntax errors ?
– Madhur Bhaiya
Nov 21 at 12:43
Error, Im getting duplicate if I re-execute the query. I should not have any duplicate results in theaudit_status_tbtable.Help pls By duplicate I mean, sameid, state_id, status , descriptionrows in some cases...
– Emanula Sohn
Nov 21 at 17:59
@EmanulaSohn it will obviously reenter the same data. One way is to put a composite unique constraint on(id, Column_changed, old_value, new_value)
– Madhur Bhaiya
Nov 21 at 18:01
how do I add this composite unique constraint? I try addingWHERE s.id NOT IN (SELECT id FROM audit_status_tb)at the end of each select statement and its seems to work...What do you think?
– Emanula Sohn
Nov 21 at 18:12
|
show 4 more comments
thanks that what I try yesterday but wasnt sure where to properly put the select statement for the insert
– Emanula Sohn
Nov 21 at 12:39
@EmanulaSohn my query should work. Let me know if you are facing any syntax errors ?
– Madhur Bhaiya
Nov 21 at 12:43
Error, Im getting duplicate if I re-execute the query. I should not have any duplicate results in theaudit_status_tbtable.Help pls By duplicate I mean, sameid, state_id, status , descriptionrows in some cases...
– Emanula Sohn
Nov 21 at 17:59
@EmanulaSohn it will obviously reenter the same data. One way is to put a composite unique constraint on(id, Column_changed, old_value, new_value)
– Madhur Bhaiya
Nov 21 at 18:01
how do I add this composite unique constraint? I try addingWHERE s.id NOT IN (SELECT id FROM audit_status_tb)at the end of each select statement and its seems to work...What do you think?
– Emanula Sohn
Nov 21 at 18:12
thanks that what I try yesterday but wasnt sure where to properly put the select statement for the insert
– Emanula Sohn
Nov 21 at 12:39
thanks that what I try yesterday but wasnt sure where to properly put the select statement for the insert
– Emanula Sohn
Nov 21 at 12:39
@EmanulaSohn my query should work. Let me know if you are facing any syntax errors ?
– Madhur Bhaiya
Nov 21 at 12:43
@EmanulaSohn my query should work. Let me know if you are facing any syntax errors ?
– Madhur Bhaiya
Nov 21 at 12:43
Error, Im getting duplicate if I re-execute the query. I should not have any duplicate results in the
audit_status_tb table.Help pls By duplicate I mean, same id, state_id, status , description rows in some cases...– Emanula Sohn
Nov 21 at 17:59
Error, Im getting duplicate if I re-execute the query. I should not have any duplicate results in the
audit_status_tb table.Help pls By duplicate I mean, same id, state_id, status , description rows in some cases...– Emanula Sohn
Nov 21 at 17:59
@EmanulaSohn it will obviously reenter the same data. One way is to put a composite unique constraint on
(id, Column_changed, old_value, new_value)– Madhur Bhaiya
Nov 21 at 18:01
@EmanulaSohn it will obviously reenter the same data. One way is to put a composite unique constraint on
(id, Column_changed, old_value, new_value)– Madhur Bhaiya
Nov 21 at 18:01
how do I add this composite unique constraint? I try adding
WHERE s.id NOT IN (SELECT id FROM audit_status_tb) at the end of each select statement and its seems to work...What do you think?– Emanula Sohn
Nov 21 at 18:12
how do I add this composite unique constraint? I try adding
WHERE s.id NOT IN (SELECT id FROM audit_status_tb) at the end of each select statement and its seems to work...What do you think?– Emanula Sohn
Nov 21 at 18:12
|
show 4 more comments
draft saved
draft discarded
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.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- 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.
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
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53412101%2fhow-to-insert-comparison-records-results-between-2-tables-into-another-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
Is new_id a primary key auto increment in audit_status_tb ?
– Madhur Bhaiya
Nov 21 at 12:35
yes, I just need to insert as from id
– Emanula Sohn
Nov 21 at 12:36