Re: Moving Data between Tables

[Baron Schwartz]

Hi John,

Kebbel, John wrote:

I moved information about our school locks (serial numbers, combinations, 
student,
etc) from FileMaker to MySQL into a table called lockers and wrote PHP pages so 
our
teachers could record the locks returned at the end of the year. Unfortunately, 
I
missed transferring close to 200 locks.

I duplicated the structure of lockers (1313 locks) as lockers2 and reloaded all 
the
information from Filemaker (1492 locks). My problem is moving the information 
from
lockers to lockers2 that was added AFTER the Filemaker migration (2nd semester
teacher, returned, paid).

This is what I'm getting set to try, but I've never seen a JOIN in an update
statement before. Am I on the right track for this?

UPDATE lockers2 SET lockers2.returned = lockers.returned, lockers2.teacher2nd =
lockers.teacher2nd, lockers2.paid = lockers.paid WHERE lockers2.serialNumber =
lockers.serialNumber;



It's not quite right.  I hope you're not about to try this for the first time on your 
production data :-)  Maybe you can grab 100 rows from each table into scratch tables 
and play with it to be sure you will get what you want.


The general syntax (with JOIN -- I can't do comma-joins) is more like

update foo
inner join bar on ...
set a = b, c = d...

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Moving Data between Tables

[Kebbel, John]

I hope you're not about to try this for the first time on your 
production data :-) 

I dumped the lockers table before beginning my experiments. If I had trashed 
lockers2 (my experimental file) in the process, I would have truncated it and 
re-inserted the 1492 records from my batch file.

-
It's not quite right. 

You're correct. I tried my UPDATE/implicit JOIN query shortly after posting my 
email.  I expected the query to fail, which it did. Not knowing how long it 
would be before someone responded, I went ahead and solved my problem with a 
PHP script. It was nice that the problem was solve-able with PHP, but it was 
frustrating to write 20 lines of PHP because I didn't know how to write the 1 
line of MySQL that would have solved my problem. 

-
update foo
inner join bar on ...
set a = b, c = d...

I run a backup of the database and associated PHP files every few nights and 
then carry a copy of the backup home to expand the PHP and incorporate the new 
things I learn about MySQL into my table structures or into batch files. You 
can be assured I'll be spending time with our old friends foo and bar and this 
update syntax between now and Monday.


Thanks again, Baron


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]