[Solved] Updating one table with values from another table

[skysnet]

I have two tables. One contains contact information, and the other contains professional information.

They can connect to each other by a column called UNIQUE_ID

A UNIQUE_ID will have only one row in contact information table but there could be multiple rows for that UNIQUE_ID in professional information.

In the ideal world, I would know how to create an SQL query to pull and filter results as I need it.

But in the absence of that, I created the same fields (Name, email, etc) on the professional information table and I'm trying to bring the personal information (PILOT_BASIC) over to the professional table (PILOT_CERT)

Code: Select all

UPDATE PILOT_CERT
SET 
PILOT_CERT.STREET_1 = PILOT_BASIC.STREET_1
FROM PILOT_BASIC AS PILOT_CERT
INNER JOIN PILOT_BASIC AS PILOT_CERT
WHERE PILOT_CERT.UNIQUE_ID = PILOT_BASIC.UNIQUE_ID

This gives syntax error.

Any help would be greatly appreciated.

[robleyd]

What is the error message?

[FJCC]

To answer the question about the error, one problem is that you are trying to join PILOT_BASIC to itself using the alias PILOT_CERT for both instances of the table. You can't have two tables with the same name. And PILOT_CERT seems to be the name of the table you are trying to update, so you have three things named PILOT_CERT in the query,
Your query:

Code: Select all

    UPDATE PILOT_CERT
    SET
    PILOT_CERT.STREET_1 = PILOT_BASIC.STREET_1
    FROM PILOT_BASIC AS PILOT_CERT
    INNER JOIN PILOT_BASIC AS PILOT_CERT
    WHERE PILOT_CERT.UNIQUE_ID = PILOT_BASIC.UNIQUE_ID

Rather than loading the data from PILOT_BASIC into PILOT_CERT, you should use queries that join the two tables.

Code: Select all

SELECT * 
FROM PILOT_BASIC INNER JOIN PILOT_CERT ON PILOT_BASIC.UNIQUE_ID = PILOT_CERT.UNIQUE_ID
WHERE SOME_CONDITION_IS_MET

[skysnet]

Thank you for your replies. Much appreciated.

If I did this

Code: Select all

SELECT * 
FROM PILOT_BASIC INNER JOIN PILOT_CERT ON PILOT_BASIC.UNIQUE_ID = PILOT_CERT.UNIQUE_ID
WHERE N_STATE = CA

Errors:
- The data content could not be loaded.
- The query can not be executed. It contains more than one table.

If I run:

Code: Select all

SELECT * 
FROM PILOT_BASIC INNER JOIN PILOT_CERT ON PILOT_BASIC.UNIQUE_ID = PILOT_CERT.UNIQUE_ID
WHERE 'PILOT_BASIC'.'N_STATE' = 'CA'

Errors:
- The data content could not be loaded.
- Syntax error in SQL expression

and if I do

Code: Select all

    UPDATE PILOT_CERT
    SET
    PILOT_CERT.STREET_1 = PILOT_BASIC.STREET_1
    FROM PILOT_BASIC AS PILOT_CERT
    INNER JOIN PILOT_BASIC AS PILOT_CERT
    WHERE PILOT_CERT.UNIQUE_ID = PILOT_BASIC.UNIQUE_ID

Error:
- The data content could not be loaded.
- syntax error, unexpected '.', expecting SQL_EQUAL

[FJCC]

The error containing "more than one table" makes me think that you are not connected to a fully functional database but to database tables based on text files or spreadsheets. Is that correct? If so, I think you will have to import the data into fully functional tables in order to do joins, which isn't very difficult. I don't have to time to explain all of it at the moment but please explain where your data are stored.

The second query has the error that 'PILOT_BASIC'.'N_STATE' uses single quotes when it should use double quotes or be unquoted.

[skysnet]

Thank you for your reply.

Tables were originally CSV. I read it is better to re-save them in .dbf format which I did and then loaded into Base.

They are currently showing up under "tables" per attachment

They were brought in by doing -> File -> New Database -> Connect to an existing database -> dBase

If I tried to "open an existing database file" it would say Please choose 'Connect to an existing database' to connect to an existing database instead.

[skysnet]

Any tips or suggestions would be much appreciated.

[FJCC]

I don't know about the functionality of dbf files when connected to Base, but your description makes me think that is limiting your ability to do joins. I still think you should get the data into real DB tables. I assume you still have the csv files. Can you open them in Calc and tell us how many lines of data there are? That will help me decide if the method I am considering explaining to you is likely to work.

[skysnet]

There are over 100K entries. I ended up loading them into SQLLiteStudio and got the results needed that way. Appreciate the help.