INSERT syntax issue


Hi thanks in advance to all who respond to this.

I have 1 database with 5 tables. The common field in all tables in the Student_Number field. PsWittStudents is a large table with all of the student names and addressses and such. PsWittMothers contains their mothers name and numbers. The PsWittStudents table has columns named Mother-First Mother-Last but there is no information there. It is all in the PsWittMothers tables. To get the data from the PsWittMothers table into the PsWittStudents tables I have been trying to use INSERT and have constantly come up short.

The code I have been trying to use is:
INSERT INTO `PsWittMothers` (`Mother-Last`,`Mother-First`,`MotherDayPhone`,`Mother_home_phone`)
SELECT `PsWittMother`.`Mother-last`,`PsWittMother`.`Mother-first`,`PsWittMother`.`Mother-work`,`PsWittMother`.`Mother-home`
FROM `PsWittMother`
WHERE `PsWittStudents`.`Student_Number`=`PsWittMother`.`Student_Number`;

I have played with the syntax on the mysql handbook site and I cannot see what I'm doing wrong. Any advice on how to insert that data would be most appreciated, as I am kind of under the gun at work about this
Posted On: Monday 29th of October 2012 05:46:00 AM Total Views:  299
View Complete with Replies

RELATED TOPICS OF MYSQL PROGRAMMING LANGUAGE




Is there a way to generate sql insert statement from directory of files?

I'm trying to make something like this: Code: (NULL, 'SeaBattle', 'gameboy', 'Gameboy', '22', 'TheSmartass', '37', 'Unsorted', '13', '1', '0', '480', '432', '', '2009-03-29 19:30:38', 'English', '', '', 'SeaBattle'), all the file names are like SeaBattle.zip or something.... They are just sitting in a directory. Can something generate this statement for me inserting all the file names I mean I am ok with doing mass find and replace to get rid of .zip and if i must I will put space into sql statement... But so far the only way I have found to do this is with "send to clipboard as name" and then tons of mass find and replace as well as manual data entry on each line. Seeing how I was stupid and wasn't remote backing up my database since getting new hard drive installed a month ago I now have countless hours of data entry ahead unless there is solution.
VIEWS ON THIS POST

150

Posted on:

Thursday 25th October 2012
View Replies!

create and insert throwing error.

Getting back into this after a couple of weeks away. I am creating a table and then trying to insert values. MySQL throws the error 1136, and says that column count doesn't match value count at row 1. what am I missing please Code: CREATE TABLE IF NOT EXISTS vat_rates ( vat_rate decimal(5,2) NOT NULL , date_from date NOT NULL , date_to date default NULL , PRIMARY KEY (vat_rate,date_from) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; INSERT INTO vat_rates (vat_rate) VALUES ('17.5', '2008-04-05', ''), ('15.00', '2008-12-01', ''); bazz
VIEWS ON THIS POST

102

Posted on:

Thursday 25th October 2012
View Replies!

How to get all the records inserted on last date

Hi all, we have the following table structure and data id user_id date food_description calories_consumed 1 112 2008-11-17 breakfast 1025 2 112 2008-11-17 lunch 4589 3 112 2008-11-17 dinner 3698 4 112 2008-11-18 morning snack 36985 6 112 2008-11-23 Lunch 215 7 112 2008-11-23 Dinner 2541 8 112 2008-11-24 Lunch 2541 9 112 2008-11-24 Dinner 142 we want to display all the records inserted last date i.e. for this example records for 2008-11-24 (2 records) what the sql query for this, we are using mysql version 4.0.26,
VIEWS ON THIS POST

201

Posted on:

Thursday 25th October 2012
View Replies!

data import and how to fight long records inserts

, I have a CSV import module implemented into a Desktop application. The App connects to Postgres/MySQL database. where I have columns such as name varchar 80 phone varchar 20 I encountered a situation when someone tries to import a name longer than 80 or a phone number longer than 20. The query fails. What idea you have to fight this situation Please note the database is heavy, has hundreds of tables, and all process is automatically created. The code is modular and I cannot implement per table/column restrictions by hand to limit the data.
VIEWS ON THIS POST

158

Posted on:

Thursday 25th October 2012
View Replies!

how to use last_insert_id() function INSIDE an INSERT query

so i've got an insert query, puts a reference to a file on the system in a table. good, great. Now i've got another table ( a join table) that has 3 columns (id, fileId, entryId) that i need to make an association on. I've got the entryId, but the fileId is what I don't have but can get with the last_insert_id() function but don't know how to 'use' it. Is this right Code: INSERT INTO siteentryattachements(entryId, fileId) VALUES ('#ARGUMENTS.entryId#','SELECT LAST_INSERT_ID() as fileId');
VIEWS ON THIS POST

132

Posted on:

Thursday 25th October 2012
View Replies!

insert...update in mysql?

I am trying to insertupdate into multiple tables how do i do that I have 3 tables e.g. table x table y table z. table x (x_id) table y( y_id, x_id) //x_id-foreign key referencing table x table z (z_id, y_id) //y_id-foreign key referencing table y
VIEWS ON THIS POST

238

Posted on:

Thursday 25th October 2012
View Replies!

Mysqli "Commands out of sync" error on insert

Getting this error for a simple mysqli_multi_query query. Basically: mysqli_multi_query("Insert INTO table (stuff); Insert INTO other_table (Other Stuff)"); Any ideas on this one. Everything I can find onthis error is related to returning output, which there should be none. Also found a few answers related to stored procedures, but definitely not the case here.
VIEWS ON THIS POST

101

Posted on:

Thursday 25th October 2012
View Replies!

mysql insert+php4 vs php 5???

Dear, I have wrote an insert query and it works fine when under php4.. However, when i move to php5, the data is not store in the database.. No error is sent althought I use mysql_error() at the connection, query to database.. May i know why is this happened How can I solve it My script is as below: PHP Code: mysql_connect("localhost","user","123741")ordie(mysql_error()); mysql_select_db("database1")ordie(mysql_error()); mysql_query("INSERTINTOrecord(ic,name,email)VALUES('".$ic."','".$usrname."','".$email."')")ordie(mysql_error()); Print"Yourinformationhasbeensuccessfullyaddedtothedatabase.";
VIEWS ON THIS POST

224

Posted on:

Thursday 25th October 2012
View Replies!

large insert on customized data

We are doing some scripting to send out bulk email from a database. The database has a table containing user email addresses along with other user information. We want to select from the user table a list of users, and then insert these into the _mail_queue table so we can do batch processing of the emails. The problem is lets say we need to send to 10,000 users. We would have to do 10,000 insert queries I understand we could do a nested select, but some of the data entered into mail_queue is going to be customized first. (Example: Dear User's First Name,) Any ideas on the best way to approach this
VIEWS ON THIS POST

95

Posted on:

Thursday 25th October 2012
View Replies!

Can I insert a record via email?

I have a form which is filled out, when it is submitted I get sent an email in the format of a pre-filled html form, In the email I have a button which inserts the dataset into the mysql table. I get the email perfectly, with the data the user entered in a form, however when I press the submit button (which takes my to the page to insert into the table this happens (screenshot) As you can see, none of the variables seem to be passed over (The forms method is POST) and each variable goes iknto the POST array PHP Code:
VIEWS ON THIS POST

99

Posted on:

Thursday 25th October 2012
View Replies!

SQL DB is inserting weird

I have created a SQL 2005 DB on my GoDaddy HostServer. I have set up a form that submits the form info into the table. I have noticed that the DB table is inputing data where it wants to, meaning it is not in the order that I had submitted the form. (form submit 1,2,3,4,5 is submitting like 2,1,4,3,5) Does anyone know why this is happening FYI, I have not set up a primary key and do not know how to at this point. Can someone guide me through this procedure, I'm sure it's very simple to do, but have not a clue on how to do it. is this causing my problem above. GoDaddy just updated from SQL 2000 to 2005, so there's a lot of stuff in there that has me confused. THANKS!!!!
VIEWS ON THIS POST

110

Posted on:

Thursday 25th October 2012
View Replies!

how to handle duplicate keys in insert

Hi people I have the following table definition: Code: CREATE TABLE `suggested_synonyms` ( `Last_Name` varchar(255) NOT NULL, `Synonym` varchar(255) NOT NULL, PRIMARY KEY (`Last_Name`,`Synonym`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; The table stores mapping from a last name to a synonym. I need to insert multiple rows into the table. Something like: Code: insert into suggested_synonyms (`Last_Name`,`Synonym`) values ('smith', 'smeeth'), ('smith', 'smeath'); the problem is that some of the mappings may already exist in the table. I tried to use the ON DUPLICATE KEY UPDATE syntax to prevent duplicate entries, but apparently I am not doing it correctly: Code: insert into suggested_synonyms (`Last_Name`,`Synonym`) values ('smith', 'smeeth'), ('smith', 'smeath') ON DUPLICATE KEY UPDATE; As I need to specify the columns to update. Is there a way to insert multiple rows in a single query and still be safe from duplicate rows
VIEWS ON THIS POST

289

Posted on:

Thursday 25th October 2012
View Replies!

double table insert issue

I have a simple form that submits into two tables. the first table would generate an id that would then be submitted into the second table along with some other info. It seemed to be working, but i have several users that can never seem to get anything to save to the main table. I look in the database and the main table will not have an entry, but the secondary table will have an entry, but the ID field will be blank. Here is the sql, could you please tell me what might be wrong\t Code: if (isset($_POST['name'])) { $name = $_POST['name']; * * $ok1 = @mysql_query("INSERT INTO spec_supplier SET * name='$name', * ctype='$ctype', * caption='$caption', * online='$online', * sidebar_title='$sidebar_title', * sidebar_content='$sidebar_content' "); $ok2 =* @mysql_query("INSERT INTO spec_categories SET sid=(last_insert_id()), catname='$name' "); * * if ($ok1 and $ok2) { echo ' Supplier added successfully.'; * } else { * echo 'Error adding submitted supplier: ' . * mysql_error() . ''; } }
VIEWS ON THIS POST

185

Posted on:

Thursday 25th October 2012
View Replies!

Force Join to return every row but insert (NULL) under condition

, not sure whether this can be done in MySQL because I know too little to know whether it's doable. Two tables: t1 and t2 t1 has column id (unique values) and t2 has columns id and UserID (unique pairs) In both tables id is a list of product IDs. In t2, UserID is a list of users who own the corresponding product id. I am trying to get a result set with columns id and UserID that lists each product id once and lists a chosen UserID if that user owns the product, but has (NULL) if the user does not own the product. Example. If t1 contains: id 1 2 3 and t2 contains id|UserID 1-|10 1-|11 1-|12 2-|10 2-|11 3-|10 If the query searches the products owned by UserID 11, the result set should be id|UserID 1-|11 2-|11 3-|(NULL) since UserID 11 does not own product id 3. I have tried the following: SELECT t1.*, t2.UserID FROM t1 LEFT JOIN t2 ON t1.id = t2.id WHERE t2.UserID = 11 Unfortunately this returns only id|UserID 1-|11 2-|11 It does not return product id 3. I have also tried: SELECT * FROM ( SELECT t1.*, t2.UserID FROM t1 LEFT JOIN t2 ON t1.id = t2.id WHERE t2.UserID = 11 UNION SELECT t3.*, t4.U_ID FROM t1 AS t3, t2 AS t4 WHERE t4.U_ID 11) AS t5 GROUP BY t5.id this returns: id|UserID 1-|11 2-|11 3-|10 so every product id is returned but the last row doesn't have (NULL) under UserID. The query also seems like an inefficient way to get at what I want, but I don't know enough to be sure. Ideas please
VIEWS ON THIS POST

89

Posted on:

Monday 29th October 2012
View Replies!

Help with inserting associated data into two tables

, I'm working on a site for a friend and as a MySQL novice, I've become a little bit stuck. My friend wants a site that will allow him to post simple adverts with up to images but I'm unsure about the best way and how to do this. After some searching on the internet, I'm really confused as some sites say to store the advert details and image info in one table and others say the best way is to use two separate tables. Since I know that keeping as much data as separate as possible is always a good idea, I've opted for two tables to store the data in. I have my form constructed and I have built my two database tables as follows: Adverts: advertID - INT (auto increment) (Primary Key) make - Varchar (256) model - Varchar (256) price - Decimal (10,0) description - Text createDate - datetime Images: The following will only store path to and filename of images. Actual files will reside in a directory. imageID - INT (auto increment) (Primary Key) advertID - INT
VIEWS ON THIS POST

103

Posted on:

Monday 29th October 2012
View Replies!

How to insert record from C into mysql table??

Welcome and thank you very much in advance for your help, I am writing a program in C (vc6), which records the user. It connects to a MySQL table and then to record the user's name and age. - the problem is - I do not know how to put a C language data in the table, because in this way does not work: >> if (mysql_query (& mysql, "INSERT INTO Login (name, old) values (s.name, s.old))) - if (s.name, s.old) is only a number or word, it's simple and works. But here is the C language VARIABLES and how these variables to insert into mysql table - example, they describe the structures, but it is not necessary. User data and can record directly to a table, in addition to structures. #include #include #include #include #include MYSQL mysql; MYSQL_RES *res; MYSQL_ROW row; void die(void){ printf("%s\n", mysql_error(&mysql)); exit(0); } void main(void){ //============================ register user ================= struct user { char name[20]; char old[20]; } s; printf("hi what is your name\n"); scanf("%s",s.name); printf("How old are you\n"); scanf("%s",s.old); //======================================================= unsigned int i = 0; if (!mysql_init(&mysql)) abort(); if (!(mysql_real_connect(&mysql,"localhost","root","pass","base", 3306, NULL, 0))) die(); if (mysql_select_db(&mysql,"base")) die(); //===================== problem here - how insert in LOGIN table user info\t ======== if (mysql_query(&mysql,"insert into LOGIN (name,old) values (s.name, s.old)")) //\t\t\t die(); //==================================================================== if (mysql_query(&mysql,"SELECT * FROM LOGIN")) die(); if (!(res = mysql_store_result (&mysql))) die(); while((row = mysql_fetch_row(res))){ for (i=0; i < mysql_num_fields(res); i++) printf("%s\t",row[i]); printf("\n"); } if (!mysql_eof(res)) die(); mysql_free_result(res); system ("pause"); mysql_close(&mysql); } Do you have any ideas in many places looking for this and there is no ... Thank you very much! Ronald.
VIEWS ON THIS POST

245

Posted on:

Monday 29th October 2012
View Replies!

Date of data insertion

Is there any way to know when records have been inserted into a mysql (5.0) database The table didn't have a field (timestamps or date) to keep record of the date. Now I am going to modify that, but for the existing data, an admin might be able to find out right
VIEWS ON THIS POST

90

Posted on:

Monday 29th October 2012
View Replies!

Page 2 - Is last_insert_id() a reliable option for a system?

Quote: Originally Posted by r937 so who designed the unique key that you're populating Look here -> http://forums.devshed.com/showpost.phpp=2480513&postcount=7
VIEWS ON THIS POST

111

Posted on:

Monday 29th October 2012
View Replies!

Trigger mysql 5.0 insert 2 rows as one row

I have trouble in trigger mysql5.0 and need help now I want to insert 2 rows from deposit into T so in tabel T it would became 1 row in one id on text field from tabel deposit, I work on phpmyadmin, it return an error like the following: Code: INSERT INTO T( tel_number, text ) VALUE( '09999', SELECT ( SELECT text FROM deposit ORDER BY id DESC LIMIT 1 , 1) AS a, ( SELECT text FROM deposit ORDER BY id DESC LIMIT 2 , 1) AS b) MySQL said: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT (
VIEWS ON THIS POST

144

Posted on:

Monday 29th October 2012
View Replies!

Mysql_insert_id for a multiple insert statement

I'm doing a multiple insert: Code: INSERT INTO (name) VALUES ('Jon'), ('Tim'), ('Mark') (Simplified example). The table has two columns, "id" and "name". "id" is primary key auto increment. I need to get the "id" values for the last three inserted value. mysql_insert_id() gives me the last one only. Is there any SQL command or PHP that will give me this Otherwise, can I rely on doing mysql_insert_id() as the last one, and then subtracting one for each row added Are there any other options (Obviously I could insert these one at once, but I'd prefer to keep the multiple insert statement).
VIEWS ON THIS POST

105

Posted on:

Monday 29th October 2012
View Replies!