How to import data into existing rows of a table?

[Reed Loefgren]

I have moved 665 records off of Filemaker into mysql-4.0.18. I had a
great deal of trouble exporting a text field, possibly because it had
control characters that interferred with my field delimiter (also it
was from a Mac, and I'm new at this.)

So I have a table called invoices and another table called tasks, that
has only an invoice number and the work billed on that invoice number.
To see the work billed along with the other invoice data I have to do
a join, and this works but is clumsy for me. Since tasks.work is a
text field of 'infinite' variety I don't see the sense of keeping it
in a separate table like I would with clients' info; it belongs *in*
the invoice table, not just with it.

Is it possible to import this text data into a field in the invoice
table? I've messed around with this but it always appends the data as
new records. Perhaps I was doing an insert back then. Is the answer
something like 'load data infile 'workdone.csv' into invoices
(workdone);'

The invoices table was filled in a certain order (by invoice number)
and the workdone.csv was exported in the same order, so each invoice
row should get its correct text.

Is this a good thing, or am I thinking too 'flat-file-ish'?

Thanks,

r

[Bill Karwin]

Reed Loefgren wrote:

Is it possible to import this text data into a field in the invoice
table?

What you want to do is add a field to the invoice table for the task
text, and UPDATE that field with the text value found in the
corresponding record that is currently in the tasks table. This is a
tricky thing to do in SQL, and in general cannot be done in one statement.

I can think of two different techniques to solve cases like this.

One method would be to generate a series of UPDATE statements, each of
which will update the value in one record of the invoice table. You can
even generate these statements as literal text from a select on the
legacy tasks table:
select concat('update invoices set task = \'', T.task_text,
'\' where invoice_number = ', T.invoice_number, ';')
from tasks T;

Capture the output of this query and then run it through mysql as a
script after you create the new column in the invoices table using ALTER
TABLE ADD COLUMN.

The other method would be to create a new table that has the same
structure as the old invoices table, plus an additional column for the
task text. Then fill that table using INSERT based on a join between
the old invoices table and the tasks table.
INSERT INTO invoices_new
SELECT I.*, T.task_text
FROM invoices I INNER JOIN tasks T using (invoice_number);

Then drop the old invoices table, and rename the new invoices table to
simply 'invoices'.

Regards,
Bill K.

[Reed Loefgren]

....

The other method would be to create a new table that has the same
structure as the old invoices table, plus an additional column for the
task text. Then fill that table using INSERT based on a join between
the old invoices table and the tasks table.
INSERT INTO invoices_new
SELECT I.*, T.task_text
FROM invoices I INNER JOIN tasks T using (invoice_number);

Then drop the old invoices table, and rename the new invoices table to
simply 'invoices'.

Regards,
Bill K.

Bill,

This I'm pretty sure I can do, and it's better than both my scripting
abilities and my understanding right now of inner/outer/left/right
joins.

thanks,

r

[Leif]

"Reed Loefgren" <rl***@interfold.com> skrev i en meddelelse
news:d3*************************@posting.google.co m...

I have moved 665 records off of Filemaker into mysql-4.0.18. I had a
great deal of trouble exporting a text field, possibly because it had
control characters that interferred with my field delimiter (also it
was from a Mac, and I'm new at this.)

So I have a table called invoices and another table called tasks, that
has only an invoice number and the work billed on that invoice number.
To see the work billed along with the other invoice data I have to do
a join, and this works but is clumsy for me. Since tasks.work is a
text field of 'infinite' variety I don't see the sense of keeping it
in a separate table like I would with clients' info; it belongs *in*
the invoice table, not just with it.

Is this a good thing, or am I thinking too 'flat-file-ish'?

You are.

One invoice can have multiple tasks, right?

Better get the hang of joins, it is really not that difficult.

Leif