Doh...what's wrong with this update using 2 tables query?

[dcnguyen]

I thought this was the correct syntax, but everytime I run it, it crashes the database:

UPDATE table1, table2 SET table1.X = table2.x_coord AND table1.Y=table2.y_coord WHERE table1.address = table2.address

It's dealing with a table with about 1000 entries in one, and 100,000 in the other.

 

[guelphdad]

it most likely isn't crashing the database but is taking a very long time.

what columns do you have indexed?

 

[johnwm]

Check out the syntax for the UPDATE statement here:

http://dev.mysql.com/doc/refman/4.1/en/update.html

You will find that you don't want the AND in your statement, just a comma instead

___________________________________________________________
If you want the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller
Steam Engine Prints

 

[dcnguyen]

As it turns out, it just took an extreme amount of time...What would the problem be? Is it because in both tables, there are addresses that appear more than once?

 

[guelphdad]

the problem as I've suggested is lack of indexes. which columns do you have indexed in your tables? I'd guess none of them beyond a primary key if you are using them.