0

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)
5
Contributors
9
Replies
47
Views
2 Years
Discussion Span
Last Post by k_manimuthu
0

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

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.

Edited by Reverend Jim

0

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)
1

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

Edited by cereal

0

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.

Edited by k_manimuthu

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.