CODEWITHSUNDEEP
mysqlsqlinnodb
mfb
mfb

Reputation: 833

Data Transfer Between Databases Using SQL Queries

I have changed the structure of my database, and need to copy data from the old instance to the new one. Since the columns of the tables have been altered I can't simply export/import the data.

I have been somewhat successful with moving the data, but I am now experiencing the following problem. Consider this sample query:

insert into gene_results (test_kit_id)
    select id
    from test_kits
    where tag = xploreyou_db_copy.gene_results.tag;

I receive the following error: Unknown column 'xploreyou_db_copy.gene_results.tag' in 'where clause'

I am absolutely positive that the column exists, and I have been successful with this syntax before. I am very confused as to why this suddenly doesn't work. I'm writing my queries in Sequel Pro. The databases are located on the same server.

Upvotes: 0

Views: 49

Answers (2)

Tharif
Tharif

Reputation: 13971

where tag = xploreyou_db_copy.gene_results.tag;

Use join instead of where in above query

INSERT INTO gene_results
                         (test_kit_id)
SELECT        test_kits.id
FROM            gene_results AS gene_results_1 INNER JOIN
                         test_kits ON gene_results_1.tag = test_kits.tag

Upvotes: 1

Madhivanan
Madhivanan

Reputation: 13700

You need to join with that database

insert into gene_results (test_kit_id)
    select t1.id
    from test_kits as t1 inner join xploreyou_db_copy.gene_results as t2
    on t1.tag = t2.tag;

Upvotes: 1

Related Questions