471,090 Members | 1,410 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,090 software developers and data experts.

How to import data into existing rows of a table?

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
Jul 20 '05 #1
3 2759
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.
Jul 20 '05 #2
....
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
Jul 20 '05 #3

"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
Jul 20 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by grawsha2000 | last post: by
4 posts views Thread by Gregor Kovač | last post: by
4 posts views Thread by db2admin | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.