I have a large (9000 records) table 'map', with a key field 'id', and a
data field 'p'.

I have a small (30 records) table 'hexads', with a unique field 'rm0',
whose value matches one of the 'id' values in 'map', and a currently
empty field 'p03' which I want to put data in.

What I want to do is
for each record of 'hexads'
look up its 'rm0' value in the 'id' field of 'map'
take the 'p' value from that record of 'map'
and put it in the 'p03' field of the record of 'hexads'

How can I do this in MySQL?

Nick
--
Nick Wedd nick (AT) maproom (DOT) co.uk

On 14-10-10 21:23, Nick Wedd wrote:

update hexads H
set H.p03=(SELECT M.p
from map M
where M.id=H.rm0 LIMIT 1)


But a better answer might be found at:
http://dev.mysql.com/doc/refman/5.1/en/update.html

--
Luuk

In message <s43ko7-akb.ln1 (AT) qqqqq (DOT) xs4all.nl>, Luuk <Luuk (AT) invalid (DOT) lan>
writes
It works! Many thanks for a very fast correct answer.

Nick

I'll have a look.
--
Nick Wedd nick (AT) maproom (DOT) co.uk

In article <fUnU4CCLj1tMFAh+@maproom.demon.co.uk>, Nick Wedd <nick (AT) maproom (DOT) co.uk> wrote:
The following would work in Access; I don't know if MySQL supports the same
syntax:

UPDATE hexads INNER JOIN map ON map.id = hexads.rm0
SET hexads.p03 = map.p
;

On 2010-10-14 21:23, Nick Wedd wrote:
Why do you need to duplicate the information?

/Lennart

[...]

On 15-10-10 02:56, Doug Miller wrote:
Now i know why i was pointing Nick toward the docs....
This UPDATE <table> INNER JOIN ....
is so much nicer then my solution

;-)

--
Luuk