Insert Into [Table] Select ... question

divined · #1 (**permalink**) 11-15-05, 06:26

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?

Nura · #2 (**permalink**) 11-15-05, 07:20

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.

felixg · #3 (**permalink**) 11-15-05, 08:46

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

divined · #4 (**permalink**) 11-15-05, 11:29

cool, I`ll try it first thing in the morning.

divined · #5 (**permalink**) 11-16-05, 02:05

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?

felixg · #6 (**permalink**) 11-16-05, 02:56

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

divined · #7 (**permalink**) 11-16-05, 03:53

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?

felixg · #8 (**permalink**) 11-16-05, 04:03

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

divined · #9 (**permalink**) 11-16-05, 06:56

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!!

Internet Services

Web Development

Digital Marketing

Consumer Tech