0

I am trying to transfer data from one database to another in the same server in the condition that one field in the old DB is equivalent to a field in the new DB

I can mention that the two databases have complete different structure.

Here is my script:

INSERT INTO newDB.myTable (field) select oldField FROM oldDB.table WHERE id= newDB.myTable (field);

I get this error:

ERROR 1305 (42000): FUNCTION newDB.myTable does not exist

Hazu
  • 105
  • 1
  • 11
  • check the spelling of db name and table name – Vijaya Vignesh Kumar Dec 08 '16 at 15:29
  • are you sure the newBD has the table myTable ? – Vijaya Vignesh Kumar Dec 08 '16 at 15:30
  • I think that the problem comes here `WHERE id= newDB.myTable (field)`. Try with `WHERE id=field` – Alee Dec 08 '16 at 15:33
  • I checked the spelling many times! i don't think it's the case – Hazu Dec 08 '16 at 15:36
  • @Alee it seems like this! i tried it now i get ERROR 1054 (42S22): Unknown column 'field' in 'where clause' – Hazu Dec 08 '16 at 15:38
  • the same unknown column 'myTable.field' error – Hazu Dec 08 '16 at 15:49
  • field must be value(s). For example `WHERE id=1` or if you have many values `WHERE id in (value1, value2,...)`.It can't be a column name. – Alee Dec 08 '16 at 16:05
  • Thing is i need to transfer only the data that meet this condition! which is the value of one field in old DB is equivalent to the a field in new DB! i don't have constant values! – Hazu Dec 08 '16 at 16:06
  • so try this. `WHERE id in (select id from oldDB.table)` – Alee Dec 08 '16 at 16:14
  • You think this will insert the value in the row where the two fields match in newDB and oldDB!? i think this will insert the value in all rows since the id will always be there in the set result of the select – Hazu Dec 08 '16 at 16:42
  • You can get the idea from [this answer](http://stackoverflow.com/a/3399710/1908331) – EhsanT Dec 08 '16 at 17:51

1 Answers1

0
newDB.myTable (field)

Should be:

newDB.myTable.field

(The brackets make MySQL think myTable is a function)

RichardAtHome
  • 4,293
  • 4
  • 20
  • 29