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)

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

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;

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.

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)

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

@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;

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

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.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.