Subscribe to this thread
Home - General / All posts - Join/Merge two tables with SQL
surveyor
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:

INSERT INTO A(Filename)

SELECT B.Filename From B JOIN A

ON B.Key = A.Key

tjhb
10,094 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]

FROM A INNER JOIN B

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

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

UPDATE

before it, and

SET [Filename} = [Filename 2]

after it.

surveyor
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.

tjhb
10,094 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.

Dimitri


7,413 post(s)
#20-Oct-18 04:23

With a comma, as in

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

surveyor
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.

Attachments:
SQLcapture.PNG

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