[SOLVED] INSERT syntax issue

[gnunoob]

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 http://dev.mysql.com 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 Thanks again

[gevans]

Try the following (delete `PsWittMother` first [maybe make a backup]);

 

CREATE TABLE `PsWittMother` AS
SELECT `Mother-Last`,`Mother-First`,`MotherDayPhone`,`Mother_home_phone`
FROM `PsWittStudents`

 

Check that the field names following the 'SELECT' are correct.

 

EDIT IF YOU ALREADY TRIED;

 

TABLES should be TABLE

[gnunoob]

I see now that I made a typo in my post. It should read as such:

 

INSERT INTO `PsWittStudents` (`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`;

 

PsWittStudents is the tables that I am trying to get the data into, PsWittMother has the data. The fields are typed correctly, First and Last are capital in PsWittMother and lower case in PsWittStudents. The two errors I have been getting when playing with the syntax are

 

1062 - Duplicate entry '' for key 1

I get this when I add `PsWittStudents` to the FROM clause and this

1054 - Unknown column 'PsWittStudents.Student_Number' in 'where clause'

When I leave it out

[gevans]

Using insert this way will attempt to add new rows to the table rather than UPDATE the current rows, try the following;

 

UPDATE table_a JOIN table_b ON table_a.column_a3 = table_b.column_b3

SET table_a.column_a1 = table_b.column_b1,

table_a.column_a2 = table_b.column_b2;

 

which should look like;

 

UPDATE `PsWittStudents`, s JOIN `PsWittMother`, m ON `s`.`Student_Number`=`m`.`Student_Number`

SET `s`.`Mother-Last`=`m`.`Mother-last`,

`s`.`Mother-First`=`m`.`Mother-first`,

`s`.`MotherDayPhone`=`m`.`Mother-work`,

`s`.`Mother_home_phone`=`m`.`Mother-home`

[gnunoob]

Thank you for your help so far. I ran the code and I ended up with an error:

 

#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 'ON `s`.`Student_Number`=`m`.`Student_Number`

SET `s`.`Mother-Last`=`m`.`Mother-' at line 1

 

I am using MySQL 5.0.51a if that makes any difference

[gnunoob]

You my friend, are a life saver. I used the full table.field name instead of setting variables and it worked great. I couldn't possibly ask for more thank you very very very much