CODEWITHSUNDEEP
mysqlsql
Zip
Zip

Reputation: 5592

Update(populate) SQL Table column with data from another database table

I have two databases, test1and test2. test1 has the following table1.

ID | Word      | Def
1.   abandon     NULL
2.   test        NULL

And for test2 database, I have the following table2.

ID | Word      | Def
1.   abandon     (verb) the meaning of abandon
2.   word2       the meaning of word2
3.   abandon     (noun) the meaning of abandon

I want to copy the definitions of words from the database test2, table2 to database test1, table1 where a word from table1 matches with the same word from table2 and avoid duplicate word; for example, the word abandon appears twice in table2 and only once in table1. If possible, I want to append the second definition of the word abandon appended to the first definition and update it in table1, Def column.

I have tried the following.

UPDATE test1.table1 
SET Def = (SELECT Def 
           FROM test2.table2 
           WHERE test1.table1.Word = test2.table2.Word);

But I am getting an error which I do not quite understand.

Upvotes: 0

Views: 455

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269593

You need to get only one definition. Here is an approach that works in MySQL and Postgres:

UPDATE test1.table1 t1
    SET Def = (SELECT Def
               FROM test2.table2 t2
               WHERE t1.Word = t2.Word
               ORDER BY id
               LIMIT 1);

The LIMIT 1 might alternatively be select top 1 or fetch first 1 row only.

Upvotes: 2

Related Questions