Subscribe to this thread
Home - General / All posts - Join/Merge two tables with SQL
27 post(s)
#19-Oct-18 23:27

With a SELECT, I can JOIN two tables based on a common key value.

But I really want to copy the column from Table B into Table A based on the JOIN.

Something like this:


SELECT B.Filename From B JOIN A

ON B.Key = A.Key


8,657 post(s)
#20-Oct-18 00:24

That’s close, but it will not do what you need.

INSERT will add new rows to table A, with the Filename column filled, all other columns blank.

You don’t want to INSERT new rows, but to UPDATE existing rows.

There are two ways to do that. The fastest is to build on your existing joined table, but to adjust it so that each row has both the source column from table B that you want to copy from, and the target column from table A that you want to copy to. They must have different names to avoid ambiguity (an alias will do that).

The SELECT should look like

SELECT A.[Filename], B.[Filename] AS [Filename 2]


ON A.[Key] = B.[Key]

Wrap that SELECT query in (...), then add


before it, and

SET [Filename} = [Filename 2]

after it.

27 post(s)
#20-Oct-18 02:49

I'm closer, but now have an error

Schema should contain at least one unique index.

That led me to a long and complicated (for me) post "UPDATING from a second table" which my brain can't handle right now. I would be happy if I could just run the SELECT with JOIN and copy/paste the results into a new table.


8,657 post(s)
#20-Oct-18 03:23

Sorry, I assumed Manifold 8. I should have asked. There’s a small but significant difference.

Add A.[mfd_id] directly after the word SELECT.

That’s all.


5,359 post(s)
#20-Oct-18 04:23

With a comma, as in

SELECT A.[mfd_id], A.[Filename], B.[Filename] AS [Filename 2]

27 post(s)
#20-Oct-18 20:01

I added the mfd_id as shown in the attached screen capture and the select works as expected. But when I take out the comments to include the UPDATE, I still get the same error "Schema should contain at least one unique index"

Then I added an index to the second table and it worked fine.


Manifold User Community Use Agreement Copyright (C) 2007-2017 Manifold Software Limited. All rights reserved.