MySQL Update/subquery problem 1

[MorganGreylock]

I've got 4 tables in mysql, one new one, and 3 old ones.

The new table is basically the other three appended together. All four have a field called update_date, which I need to copy from the three old tables into the one new one.

Before you say 'mysql doesn't support subqueries', note that I just upgraded to 4.1.7 today, so that does support subqueries =) ... but onto the problem:

When I run the following query, it works just fine. The problem is, when I run it again for the second old table, the update_date field in the new table gets blanked out. Here is my query:

update newtable
set update_date = (select update_date from oldtable2
where newtable.indexfield = oldtable2.indexfield);

So like I said, this works, but it blanks out the data from
the other updates (from oldtable1).

Anyone have any suggestions? I tried using a join, but was
unsuccessful to say the least.

Thanks in advance,
MG

 

[swampBoogie]

It will set the update_date column to null when there is no matching row in oldtable2. Add a where clause so only matching rows are updated.

update newtable
set update_date = (select update_date from oldtable2
where newtable.indexfield = oldtable2.indexfield)
where indexfield 
in (select indexfield from oldtable2)

 

[MorganGreylock]

Thanks, that worked perfectly.
I knew it could be done, but wasn't sure of the exact
syntax.

Thanks again,
MG