Copy missing column values from table1 to table2

[k_manimuthu]

Hi All,

I want to copy the missing 'data' field from 'tb1' to 'tb2' based on the 'id' value.
Please suggest the possible queries.

mysql> select * from tb1;
+------+------+
| id   | data |
+------+------+
|    1 | A    |
|    2 | B    |
|    3 | C    |
+------+------+
3 rows in set (0.00 sec)

mysql> select * from tb2;
+------+------+
| id   | data |
+------+------+
|    1 | NULL |
|    2 | NULL |
|    3 | NULL |
+------+------+
3 rows in set (0.00 sec)

[arunkumar.am]

UPDATE table_2
SET data = table_1.data
FROM table_1
WHERE table_1.ID= table_2.id

[k_manimuthu]

Thanks Arunkumar, the above query not working in mysql and below query working fine this task. I wish to know is any other query do the same work.

update tb1 join tb2 on tb1.id=tb2.id set tb2.data=tb1.data;

[Reverend Jim]

You can try

UPDATE tb2
   SET tb2.data = tb1.data
  FROM tb1,tb2 WHERE tb1.id = tb2.id AND tb2.data IS NULL

This will update only records for which data is NULL.

[k_manimuthu]

Hi Reverend Jim,

I got the below errror when i executed the suggested query.

mysql> UPDATE tb2
    ->    SET tb2.data = tb1.data
    ->   FROM tb1,tb2 WHERE tb1.id = tb2.id AND tb2.data IS NULL
    -> ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM tb1,tb2 WHERE tb1.id = tb2.id AND tb2.data IS NULL' at line 3
mysql> select Version();
+-----------+
| Version() |
+-----------+
| 5.0.77    |
+-----------+
1 row in set (0.00 sec)

[lps]

Maybe try this

UPDATE tb2
SET data = tbl1.data
FROM tb1 as 'tbl1'
WHERE
   tb2.id = tbl1.id

I done this by refering http://stackoverflow.com/questions/4920394/how-to-update-data-in-one-table-from-corresponding-data-in-another-table-in-sql

[k_manimuthu]

Hi lps, I tried the above command I got the same error [ERROR 1064 (42000)]

[cereal]

@k_manimuthu

Hi,

the FROM statement is not allowed in the update query, this is reason you get the error. This will work:

UPDATE tb2,tb1 SET tb2.data = tb1.data WHERE tb1.id = tb2.id AND tb2.data IS NULL;

Which is almost the same of your previous query with Reverend Jim fix to match nulls. In alternative you can use a subquery:

UPDATE tb2 SET tb2.data = (SELECT data FROM tb1 WHERE tb1.id = tb2.id) WHERE tb2.data IS NULL;

[Reverend Jim]

Try this version

UPDATE tb2
   SET tb2.data = tb1.data
  FROM tb1 INNER JOIN tb2 ON tb1.id = tb2.id AND tb2.data IS NULL

[k_manimuthu]

Hi Reverend Jim, I get syntax error when i executed the query in my MySQL version.
@cereal: Your 2 queries working fine for me.

Thanks for your time for this post. I marked the post as solved.