SQL update from one Table to another where ID matches

Question

I have 2 databases Alpha and Beta. Each database has identical table "Comments". The Comments table has 2 fields: ID and Comments. Alpha.Comments has only ID field populated, corresponding Comment field is empty. Beta.Comments has both ID and the corresponding comments. Is there SQL Query that can update all of Alpha.Comments field with appropriate entries from Beta, where ID matches?

Copy table data

Does this answer your question? [Join results from tables with same name from different databases](https://stackoverflow.com/questions/1915238/join-results-from-tables-with-same-name-from-different-databases) — Heather, Sep 21 '20 at 18:30
[Why should I tag my DBMS](https://meta.stackoverflow.com/questions/388759/) — , Sep 21 '20 at 18:30
SQL Server 2012, using MSSMS Ran this quiery: update alpha.comments set comments = (select b.comments from beta.comments b where b.id = comments.id); It errored out: Msg 512, Level 16, State 1, Line 1 Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. The statement has been terminated. — matrixman001, Sep 21 '20 at 21:52
Resolved. Apparently Table B had more records than Table A. Had to properly use Where statement to filter out the same set of records to match with Table A. — matrixman001, Sep 21 '20 at 22:06
Perhaps you would care to [answer your own question](https://stackoverflow.com/help/self-answer) and explain exactly how you resolved the problem, just in case someone else may arrive at this question as a result of encountering the same error and searching for an answer. — Abra, Sep 22 '20 at 10:14

Gordon Linoff · Answer 1 · 2020-09-21T22:05:12.247

0

Something like:

update alpha.comments
    set comments = (select b.comments from beta.comments b where b.id = comments.id);

In SQL Server, you can can express this as:

update a
    set comments = b.comments
    from alpha.comments a join
         beta.comments b
         on a.id = b.id;

This handles duplicates in an unfortunate way. It arbitrarily chooses one for the assignment, so no error is generated.

edited Sep 21 '20 at 22:05

answered Sep 21 '20 at 18:19

Gordon Linoff

1,242,037
58
646
786

1

I think you misunderstood the question. There are two tables which are both named `Comments`. There is no `beta` table. – Abra Sep 21 '20 at 18:22
@Abra . . . I tweaked the answer. That the second table is called `beta.comments` and not `beta` is a small detail on the answer. – Gordon Linoff Sep 21 '20 at 19:00
It errored out: Msg 512, Level 16, State 1, Line 1 Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. The statement has been terminated. – matrixman001 Sep 21 '20 at 21:54
1

@matrixman001 . . . If you have repeated ids in the second table, then your problem is not well defined. You should explain how you want to handle that. Nothing in your question suggests that you have repeated ids in either table. – Gordon Linoff Sep 21 '20 at 22:03

Heather · Answer 2 · 2020-09-21T18:26:56.200

0

There are already post similar to this request - please take a look at:

Copy data from one column to other column (which is in a different table)

MySQL: Copy table to another table with an extra column

Join results from tables with same name from different databases

am sure there are others, so you can find one which is related to the DB that you are using.

edited Sep 21 '20 at 18:26

answered Sep 21 '20 at 18:22

Heather

133
4
15

Why don't you vote to close the question as a duplicate? – Abra Sep 21 '20 at 18:26
Will do - missed that option – Heather Sep 21 '20 at 18:27

score 0 · Answer 3 · answered Dec 06 '22 at 05:01

0

You can update like this

UPDATE Alpha SET Comments=(
SELECT Comments
FROM Beta
WHERE Beta.ID = Alpha.ID
);

answered Dec 06 '22 at 05:01

Pawan Verma

1,152
14
22

SQL update from one Table to another where ID matches

3 Answers3