I have one mysql table which originally contained some 1200 rows of
information (an astronomical catalogue) with 60 columns (it was
populated by a load local infine from an ASCII dump of a FITS table ...
FITS is a binary astronomical format).

One of the 60 columns (which is a unique index) is a sequential auto
increment counter not present in the original data.

Now I wanted to add 15 more columns (for the same 1200 objects), and
that I did in an inelegant but effective way, i.e. I altered the table
definition and created the new columns, then took the ASCII file dumped
from a new FITS table, passed it through an awk script which generated
a file of mysql commands

update mytable set new1=val1, new2=val2 ..... new15=val15
where id=nnn and field=xxx

(nnn are internal identifiers, and xxx corresponds to an area on the
sky, actually I have 20 different areas each one with its FITS file)

Then I execute those commands and I'm done ... but ...

There is always a "but". While for 19 of my 20 fields my input files
contains exactly the same information I already had in the first 59
columns, plus the 15 extra, for ONE field the content is different
(the new one is correct, what I had before was run with a not final
version of the s/w due to a mistake of my graduate student).

There are 92 sources for which even the 59 original columns have
slightly different values, 4 sources not in the original table, and 2
sources in the original table no longer in the new one which shall be
deleted.

It was rather easy to spot them reading the new data in a temporary
table and doing some check on a join with the old table.

Addition of the 4 and deletion of the 2 could be also done
semi-manually.

I'm more concerned of the update of the 92. I want to insert the new
values in 58+15 columns of the original table (for the same field,id
combination) PRESERVING the seq column (which is autoincrement !) and
another column (which is not in the input data, but it's a flag
indicating the presence of some external file).

Of course I could use the same approach I used to fill the new 15
columns, i.e. have an awk program write 92 statements

update mytable set old1=xxx1 old2=xxx2 .... old58=xxx58
new1=val1, new2=val2 ..... new15=val15
where id=nnn and field=xxx

but this seems unelegant to me. Could it be done INSIDE mysql reading
the new data in a temptable ?

I believe I could do that with a
replace origtable select * from temptable

IF the temptable in which I load the new data HAD a unique index column
with exactly the same values of the original table, and a flag column
also with the same values of the original table

But how can do something like

update temptable set seq=origtable.seq, flag=origtable.flag
for all cases where temptable.id = origtable.id ?

(forget about field, I can use instead of origtable a copy of subset of
it with the affected field only)

I can do it MANUALLY e.g. update temp set seq=173 where id=7
but how can I update a table from another table based on some joint
selection ?

--
----------------------------------------------------------------------
nospam (AT) ifctr (DOT) mi.cnr.it is a newsreading account used by more persons to
avoid unwanted spam. Any mail returning to this address will be rejected.
Users can disclose their e-mail address in the article if they wish so.