Results 1 to 6 of 6
  1. #1
    Join Date
    May 2007
    Posts
    2

    Unanswered: connecting 2 tables

    Sorry for this very simple question - I'm new to mySQL - I have 2 table and want to populate data in one table's field with a column from another. In the documentation, i see that i cannot use the ENUM type and just point it to the other table - is there another way to accomplish this?

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    INSERT INTO Table1(Column1)
    SELECT Column2
    FROM Table2
    [WHERE ...]
    I think that'll do what you want - make sure you have the same data type for the columns!
    George
    Home | Blog

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Note: Ok, you don't necessarily have to have columns of the same datatype, because you can convert your results before inserting them
    Code:
    INSERT INTO Table1(Column1)
    SELECT CONVERT(decimal(7,2), Column2)
    FROM Table2
    HTH
    George
    Home | Blog

  4. #4
    Join Date
    May 2007
    Posts
    2

    thanks georgev

    because I'm new to mySQL - i prob didn't ask the question right - might be easier to post the code for my 2 tables and explain where the connection is:

    Code:
    CREATE TABLE `fonts` (
      `fontid` int(9) NOT NULL auto_increment,
      `fontname` varchar(16) collate latin1_bin NOT NULL default '',
      `foundryname` varchar(24) collate latin1_bin NOT NULL default '',
      `styles` enum('regular','roman','bold','italic') collate latin1_bin NOT NULL default 'regular',
      `enteredby` int(11) NOT NULL default '0',
      `entrydate` timestamp NOT NULL default CURRENT_TIMESTAMP,
      `editdate` date NOT NULL default '0000-00-00',
      `status` enum('entered','validated') collate latin1_bin NOT NULL default 'entered',
      `keywords` enum('sample','grunge') collate latin1_bin NOT NULL default 'sample',
      PRIMARY KEY  (`fontid`)
    ) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=latin1 COLLATE=latin1_bin
    
    CREATE TABLE `foundry` (
      `foundryid` int(11) NOT NULL auto_increment,
      `entrydate` timestamp NOT NULL default CURRENT_TIMESTAMP,
      `editdate` date NOT NULL default '0000-00-00',
      `foundryname` varchar(24) collate latin1_bin NOT NULL default '',
      `foundryurl` varchar(24) collate latin1_bin NOT NULL default '',
      `addrline01` varchar(24) collate latin1_bin NOT NULL default '',
      `addrline02` varchar(24) collate latin1_bin NOT NULL default '',
      `city` varchar(16) collate latin1_bin NOT NULL default '',
      `state` enum('AK','AL','AR','AZ','CA','CO','CT','DC','DE','FL','GA','HI','IA','ID','IL','IN','KS','KY','LA','MA','MD','ME','MI','MO','MN','MI','MT','NC','ND','NE','NH','NJ','NM','NV','NY','OH','OK','OR','PA','RI','SC','SD','TN','TX','UT','VA','VT','WA','WI','WV','WY') collate latin1_bin NOT NULL default 'AL',
      `zip` int(5) NOT NULL default '0',
      `country` varchar(16) collate latin1_bin NOT NULL default '',
      `phonearea` int(3) NOT NULL default '0',
      `phonenum` int(7) NOT NULL default '0',
      `contactfirst` varchar(16) collate latin1_bin NOT NULL default '',
      `contactlast` varchar(16) collate latin1_bin NOT NULL default '',
      `contacttitle` varchar(16) collate latin1_bin NOT NULL default '',
      `contactemail` varchar(24) collate latin1_bin NOT NULL default '',
      `notes` text collate latin1_bin NOT NULL,
      PRIMARY KEY  (`foundryid`)
    ) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=latin1 COLLATE=latin1_bin
    goal would be to create data in table foundry, field foundryname first and then have that foundryname populate something like an ENUM foundryname field in the fonts table.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by mistersandwiches
    ... i see that i cannot use the ENUM type and just point it to the other table - is there another way to accomplish this?
    yes, a foreign key
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    for a foreign key constraint to work in MySQL you would need to use InnoDB or some other table type which supports foreign keys

    however you don't need a foreign key constraint to be enforcred int he db IF you opt to do it programatically... it just means you have to do a bit more work to ensure the data remains consistent.

    you dont need a foreign key constraint when using a join (or for that matter the old style where predicate)
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •