|
|
Posted by Jim Michaels on 02/24/06 03:27
"strawberry" <1@2.com> wrote in message
news:QH1Lf.13991$gB4.10999@newsfe4-gui.ntli.net...
> the columns are
>
> location, longitude, latitude,
>
> i want to makeinto
> location a, location b.
>
> where each location is mapped to each other ie, point a has conenctions to
> b -z, b has connections to a, and c-z.
alter your table and create an id column loc_id as auto_increment, PRIMARY
KEY.
create another table as follows:
CREATE TABLE connections (
conn_id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
conn_from INTEGER UNSIGNED NOT NULL DEFAULT '0',
conn_to INTEGER UNSIGNED NOT NULL DEFAULT '0',
PRIMARY KEY(conn_id),
FOREIGN KEY (conn_from) REFERENCES locations(loc_id) ON DELETE CASCADE
ON UPDATE NO ACTION,
FOREIGN KEY (conn_to) REFERENCES locations(loc_id) ON DELETE CASCADE ON
UPDATE NO ACTION,
) ENGINE=InnoDB;
with the FOREIGN KEY constraints (optional) you must insert the locations
first before inserting the connections.
point a can have as many connections as it needs. just insert rows with
conn_from=loc_id of location a.
of course, if you don't plan to have that many locations in the future and
you can get by with a single char, then just change the types above from
integer to CHAR(1).
>
> "Simon" <simon@webworx.co.uk> wrote in message
> news:M91Lf.69546$0N1.44349@newsfe5-win.ntli.net...
>> can you be more specific?
>>
>> What are your columns what do you want to copy and to where..
>>
>>
>>
>> "strawberry" <1@2.com> wrote in message
>> news:vjZKf.11778$bw1.10372@newsfe2-win.ntli.net...
>>> old table has place names, say a to z.
>>>
>>> new table has to list all posssible connections, ie, a-b ,a-c , b -a .
>>>
>>> can anyone point me in the right direction for how to do this?
>>>
>>> tia.
>>>
>>>
>>
>>
>
>
Navigation:
[Reply to this message]
|