Hi,
I have one table tableNames ID,name, new_name. The columns ID and name are populated. I would like to update the field new_name from an other table tempName (ID,nameUpdated).
The ID between the 2 tables are the same, but I don't have the info for all the ID. (so 568 rows in my first table only 550 in my second one)
I saw in the MySQL cookbook different solutions, creating a new table from joining the 2 firsts, writing a script to do this....But this book also says that with the subquery in MySQL 4.1 it should be possible through a direct query.
As I updated to 4.1, I would like to give it a try.
Here is what I want to do:
update tableName set new_name= (select nameUpdated from tableName,tempName where tableName.ID=tempName.ID)
First I have here the subquery error detailed in the manual: "UPDATE t1 SET column2 = (SELECT MAX(column1) FROM t1);
It's okay to use a subquery for assignment within an UPDATE statement, since subqueries are legal in UPDATE and DELETE statements as well as in SELECT statements. However, you cannot use the same table, in this case table t1, for both the subquery's FROM clause and the update target. "
and second how can I specify update for each ID?
I tried the same with insert into tableName select nameUpdated from tableName, tempName where tableName.ID=tempName.ID, but of course the records are inserted at the end of the first table.
I tried to find some help from the manual but saw nothing, and I think this is a common problem (as mentionned in the cookbook) but I can't find any solution and I'm becoming crazy trying to combine that 2 tables!!!
If somebody could help on this query, but also give me some pointer to a good doc/book regarding subqueries?
Thanks a lot for any help.
_________________________________________________________________
It's fast, it's easy and it's free. Get MSN Messenger today! http://www.msn.co.uk/messenger
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
