Results 1 to 9 of 9
  1. #1
    Join Date
    Apr 2004
    Posts
    110

    Unanswered: Insert Into [Table] Select ... question (Solved)

    Hello everybody

    I`d like insert some fields from a table into another. The problem is that I`d also like some other fields to remain constant throughout each inserted record. Something like this :

    Insert Into Table Values (Null, Select Field From Table1 Where [clause],1,1);

    Is this a valid MySQL statement and if not can I do something else to the get the result above?
    Last edited by divined; 11-16-05 at 07:59.

  2. #2
    Join Date
    Oct 2005
    Posts
    21

    Insert

    Hi,
    You could give the name of the fields to be insert so that other fields remain unchanged.
    Ex:
    INSERT INTO Table(field1,field2) VALUES('', '');

    field1 and field2 are the name of the field in table to be inserted.Now if one filed is to be constant the give the field name and the constant value.
    I think it solves your problem if i have understood you correctly.

  3. #3
    Join Date
    Apr 2005
    Location
    Lier, Belgium
    Posts
    122
    Quote Originally Posted by divined
    Insert Into Table Values (Null, Select Field From Table1 Where [clause],1,1);
    Code:
    INSERT INTO Table
    SELECT
        NULL,
        Field,
        1,
        1
    FROM Table1
    WHERE [clause];
    --
    felix

  4. #4
    Join Date
    Apr 2004
    Posts
    110
    cool, I`ll try it first thing in the morning.

  5. #5
    Join Date
    Apr 2004
    Posts
    110
    One further thing. In the SQL statement below :

    Code:
    INSERT INTO Table
    SELECT
        NULL,
        Field,
        1,
        1
    FROM Table1
    WHERE [clause];
    can I also tell MySQL that I only want the Insert to proceed if Field does not already exist in Table. Otherwise only the offending record should be discarded and MySQL should proceed to Insert the next record in Table1.

    Can this be done with one statement?

  6. #6
    Join Date
    Apr 2005
    Location
    Lier, Belgium
    Posts
    122
    Quote Originally Posted by divined
    can I also tell MySQL that I only want the Insert to proceed if Field does not already exist in Table. Otherwise only the offending record should be discarded and MySQL should proceed to Insert the next record in Table1.
    Put a UNIQUE index on Field and use INSERT IGNORE INTO ...

    --
    felix

  7. #7
    Join Date
    Apr 2004
    Posts
    110
    You mean that I should cerate the table as this :

    Code:
    CREATE TABLE EGGYHSH(Kwd_Eggyhshs INTEGER UNSIGNED AUTO_INCREMENT,
    		                 Kwd_Mhxanhmatos VARCHAR(7),
    	 	                 Liksi_Eggyhshs BOOL,
                       	         Eidopoihsh BOOL,
    		                 INDEX Kwd_Mhxanhmatos_ind (Kwd_Mhxanhmatos) UNIQUE,
    		                 FOREIGN KEY(Kwd_Mhxanhmatos REFERENCES            MHXANHMA(Kwd_Mhxanhmatos) ON DELETE SET CASCADE),
    		                PRIMARY KEY(Kwd_Eggyhshs)
    		     ) TYPE = INNODB;
    Is this syntactically correct?

  8. #8
    Join Date
    Apr 2005
    Location
    Lier, Belgium
    Posts
    122
    Quote Originally Posted by divined
    Is this syntactically correct?
    The CREATE TABLE syntax is explained here: http://dev.mysql.com/doc/refman/4.1/...ate-table.html

    Code:
    ... UNIQUE  Kwd_Mhxanhmatos_ind (Kwd_Mhxanhmatos) ...
    will probably work.

    --
    felix

  9. #9
    Join Date
    Apr 2004
    Posts
    110
    This is how I finally cerated the table :

    Code:
    CREATE TABLE Eggyhsh(Kwd_Eggyhshs INTEGER UNSIGNED AUTO_INCREMENT,
    		     Kwd_Mhxanhmatos VARCHAR(7),
    		     Liksi_Eggyhshs BOOL,
    		     Eidopoihsh BOOL,
    		     UNIQUE INDEX Kwd_Mhxanhmatos_ind (Kwd_Mhxanhmatos),
    		     FOREIGN KEY(Kwd_Mhxanhmatos) REFERENCES Mhxanhma(Kwd_Mhxanhmatos) ON DELETE CASCADE,
    		     PRIMARY KEY(Kwd_Eggyhshs)) TYPE = INNODB;
    thx, to everyone that helped!!

Posting Permissions

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