[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