[Novalug] yet another mysql question
Igor Birman
igor_birman@yahoo.com
Tue Apr 19 06:42:14 EDT 2011
Sql in general is not great at working with row numbers, it is much better with keys. However, I can generate a row number using this syntax:
set @row=0;
SELECT *,@row:=@row+1 'row' FROM `test2`
but I don't know of a way to join to another table by row number. I would suggest adding a column to the table, then populating that column with the row number.
Assuming Table test1 has an address field that is populated, and test2 has the same address field that is blank, and assuming both tables otherwise have the same rows in the same order:
ALTER TABLE `test1` ADD `rownum` INT NOT NULL;
set @row=0;
UPDATE test1 set rownum=@row:=@row+1;
ALTER TABLE `test2` ADD `rownum` INT NOT NULL;
set @row=0;
UPDATE test2 set rownum=@row:=@row+1;
update test2
SET address = (select address from test1 where test2.rownum=test1.rownum)
Igor
Igor Birman
http://www.cyberigor.com || http://www.pbase.com/ibirman || http://www.MyYellowPad.com
________________________________
From: Bonnie Dalzell <bdalzell@qis.net>
To: NOVALUG <novalug@calypso.tux.org>
Sent: Tuesday, April 19, 2011 12:07 AM
Subject: [Novalug] yet another mysql question
in learning mysql i have made several different versions of my large
database so I can learn things and always have a original.
i have a similar table in two different databases and in one a column
is empty and in the other it has data
i would like to copy the column of data to the database with the empty
column matching by the names of the record (row) not the id number of the
record
select into is supposed to be what will work.
so far I do not have the command formatted correctly
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Bonnie Dalzell, MA
mail:5100 Hydes Rd PO Box 60, Hydes,MD,USA 21082-0060|EMAIL:bdalzell@qis.net
Freelance anatomist, vertebrate paleontologist, writer, illustrator, dog
breeder, computer nerd & iconoclast... Borzoi info at www.borzois.com.
HOME www.batw.net ART bdalzellart.batw.net BUSINESS www.boardingatwedge.com
_______________________________________________
Novalug mailing list
Novalug@calypso.tux.org
http://calypso.tux.org/mailman/listinfo/novalug
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://lists.firemountain.net/pipermail/novalug/attachments/20110419/d30640f0/attachment.htm>
More information about the Novalug
mailing list