Just simply copying one column from one table to another.
Posted by:
Alex Bokov
Date: March 24, 2009 09:53PM
I have a table, let's call it Foo, with a column of type int(11), let's call it Crud. I want to overwrite Foo.Crud with Bar.Var. Var is also type int(11), and happens to contain 1's and 2's.
Of course I did my googling like a good newbie and got the following:
update Foo,Bar set Foo.Crud = Bar.Var;
This ran without error, but when I check it by running...
SELECT COUNT( * ) AS Rows, Crud FROM Foo GROUP BY Crud ORDER BY Crud;
+------+------+
| Rows | Crud |
+------+------+
| 8801 | 1 |
+------+------+
And it's NOT because Bar.Var only contains 1's. It contains 1's and 2's!
SELECT COUNT( * ) AS Rows, Var FROM Bar GROUP BY Var ORDER BY Var;
+------+------+
| Rows | Var |
+------+------+
| 5043 | 1 |
| 3758 | 2 |
+------+------+
What am I doing wrong? Is it me or MySQL that's hopelessly insane for being unable to do something seeming so trivial?
By the way, Foo.Crud is a foreign key, and 1 and 2 are both valid primary keys in a table I'll call Ref_Crud (which has only those two rows). Could *that* have something to do with it?
Thanks in advance, MySQL gurus. I welcome being pointed to further reading, or even just a good search term just as much as an actual answer, and when I get to the bottom of this, I will post what finally worked.