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:  462
View Complete with Replies

Related Messages:

Is there a way to generate sql insert statement from directory of files?   (254 Views)
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 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.
create and insert throwing error.   (201 Views)
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
How to get all the records inserted on last date   (318 Views)
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,
data import and how to fight long records inserts   (265 Views)
, 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.
how to use last_insert_id() function INSIDE an INSERT query   (297 Views)
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');
insert...update in mysql?   (356 Views)
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
Mysqli "Commands out of sync" error on insert   (172 Views)
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.
mysql insert+php4 vs php 5???   (327 Views)
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.";
large insert on customized data   (163 Views)
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
Can I insert a record via email?   (169 Views)
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:
SQL DB is inserting weird   (190 Views)
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!!!!
how to handle duplicate keys in insert   (421 Views)
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
double table insert issue   (327 Views)
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 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() . ''; } }
Force Join to return every row but insert (NULL) under condition   (157 Views)
, 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 = 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 = 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 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
Help with inserting associated data into two tables   (177 Views)
, 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
How to insert record from C into mysql table??   (421 Views)
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.old))) - if (, 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",; 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 ======== if (mysql_query(&mysql,"insert into LOGIN (name,old) values (, s.old)")) // 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.
Date of data insertion   (191 Views)
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
Page 2 - Is last_insert_id() a reliable option for a system?   (180 Views)
Quote: Originally Posted by r937 so who designed the unique key that you're populating Look here ->
Trigger mysql 5.0 insert 2 rows as one row   (270 Views)
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 (
Mysql_insert_id for a multiple insert statement   (233 Views)
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).