List:General Discussion« Previous MessageNext Message »
From:Gleb Paharenko Date:November 13 2004 10:17am
Subject:Re: Problem with an insert query
View as plain text  
Hello.

The INSERT ... VALUES and INSERT ... SET forms of the statement insert rows based on
explicitly specified values. So you can't use select in the value clause.
Instead you should use insert ... select. By the way: don't forget to
escape single quotes in text which you insert.
See:
  http://dev.mysql.com/doc/mysql/en/INSERT_SELECT.html
And good answer of Paul DuBois at:
  http://www.mail-archive.com/mysql@ style="color:#666">stripped/msg26639.html


GH <garyhotko@stripped> wrote:
> In my database I have the following tables:
> +-----------------+
> | Tables_in_AHRC  |
> +-----------------+
> | Attendance      |
> | Participants    |
> | ProgressNotes   |
> | Sessions        |
> | Staff           |
> | StaffAttendance |
> +-----------------+
> 
> I am trying to insert data in the ProgressNotes Table using the
> following query but it does not work. Can someone please assist? Thank
> You.
> 
> INSERT INTO ProgressNotes
>        (
>        DateOfReport,
>        NumPartReg,
>        NumPartPresent,
>        NumStaffPresent,
>        NumVolPresent,
>        TodaysActivities,
>        DescribeSession,
>        Interactions,
>        Comments,
>        Plans,
>        Signed
> )
> VALUES (
>        '1', 
>        Select Count(*) From Attendance WHERE Session = 1, 
>        Select Count(*) From Attendance WHERE Session = 1 AND Present = 'Yes',
>        Select Count(*) From StaffAttendance WHERE Session = 1 AND Present = 'Yes',
>        '0',
>        'Attempt to start program',
>        'Due to the lack of completed paperwork between RRRR and SITE, we
> were unable to complete the program as schedule. We informed the
> consumers of the situation.',
>         'Understandably, the consumers were upset. The Dean (SR) was kind
> enough to try and help explain to our members that there was a
> communications problem that caused the paperwork not to be completely
> in place before we started the program. LL and I got the members'
> phone numbers so that we can advise them if we are canceled next week.
> 
> One consumer, DSL, was being uncooperative with us through out the
> time we were in session. She was pouting and having an attitude with
> all who were there including: the parents of another consumer, Dean SR
> and RRRR staff. I attempted to calm Ms. L down and stated that I was
> informed by MR that there might be a problem at 6:45PM on Friday.
> After the close of business hours on Friday. Ms. L stated on many
> occasions that M should of told her that we were not having class
> today. She called a couple of persons including a counselor at ZZZ, to
> which I was able to speak and explain the situation. I attempted to
> explain the entire situation and that we did not definitely know that
> they were not going to let us in to the room  until today. The
> counselor seemed to be understanding. I told her that we are expecting
> to run the program next week and that we would contact Ms. L if we are
> told that we can not.',
>        'Everyone (Consumers, Parents and Staff) wishes to have the program
> by next week please help us get up and running.',
>        'Attempt to start up the program next session.', 
>        'GMH, PgmLdr'
> );
> 
> 
> mysql> describe ProgressNotes;
> +------------------+--------------+------+-----+---------+-------+
> | Field            | Type         | Null | Key | Default | Extra |
> +------------------+--------------+------+-----+---------+-------+
> | DateOfReport     | int(2)       |      | PRI | 0       |       |
> | NumPartReg       | int(2)       |      |     | 0       |       |
> | NumPartPresent   | int(2)       |      |     | 0       |       |
> | NumStaffPresent  | int(1)       |      |     | 0       |       |
> | NumVolPresent    | int(2)       |      |     | 0       |       |
> | TodaysActivities | varchar(255) |      | MUL |         |       |
> | DescribeSession  | text         |      |     |         |       |
> | Interactions     | text         |      |     |         |       |
> | Comments         | text         |      |     |         |       |
> | Plans            | text         |      |     |         |       |
> | Signed           | text         |      |     |         |       |
> +------------------+--------------+------+-----+---------+-------+
> 11 rows in set (0.00 sec)
> 
> 
> 
> [QUOTE PHPMYADMIN RESULTS] 
> 
> Database AHRC  - Table Participants  running on localhost
> 
> Error
> 
> There seems to be an error in your SQL query. The MySQL server error
> output below, if there is any, may also help you in diagnosing the
> problem
> 
> ERROR: Unclosed quote @ 2040
> STR: '
> SQL: INSERT INTO ProgressNotes
>        (
>        DateOfReport,
>        NumPartReg,
>        NumPartPresent,
>        NumStaffPresent,
>        NumVolPresent,
>        TodaysActivities,
>        DescribeSession,
>        Interactions,
>        Comments,
>        Plans,
>        Signed
> )
> VALUES (
>        '1', 
>        Select Count(*) From Attendance WHERE Session = 1, 
>        Select Count(*) From Attendance WHERE Session = 1 AND Present = 'Yes',
>        Select Count(*) From StaffAttendance WHERE Session = 1 AND Present = 'Yes',
>        '0',
>        'Attempt to start program',
>        'Due to the lack of completed paperwork between RRRR and SITE, we
> were unable to complete the program as schedule. We informed the
> consumers of the situation.',
>         'Understandably, the consumers were upset. The Dean (SR) was kind
> enough to try and help explain to our members that there was a
> communications problem that caused the paperwork not to be completely
> in place before we started the program. LL and I got the members'
> phone numbers so that we can advise them if we are canceled next week.
> 
> One consumer, DSL, was being uncooperative with us through out the
> time we were in session. She was pouting and having an attitude with
> all who were there including: the parents of another consumer, Dean SR
> and RRRR staff. I attempted to calm Ms. L down and stated that I was
> informed by MR that there might be a problem at 6:45PM on Friday.
> After the close of business hours on Friday. Ms. L stated on many
> occasions that M should of told her that we were not having class
> today. She called a couple of persons including a counselor at ZZZ, to
> which I was able to speak and explain the situation. I attempted to
> explain the entire situation and that we did not definitely know that
> they were not going to let us in to the room  until today. The
> counselor seemed to be understanding. I told her that we are expecting
> to run the program next week and that we would contact Ms. L if we are
> told that we can not.',
>        'Everyone (Consumers, Parents and Staff) wishes to have the program
> by next week please help us get up and running.',
>        'Attempt to start up the program next session.', 
>        'GMH, PgmLdr'
> );
> 
> SQL-query : 
> 
> INSERT INTO ProgressNotes ( DateOfReport, NumPartReg, NumPartPresent,
> NumStaffPresent, NumVolPresent, TodaysActivities, DescribeSession,
> Interactions, Comments, Plans, Signed ) VALUES ( '1', Select Count(*)
> From Attendance WHERE Session = 1, Select Count(*) From Attendance
> WHERE Session = 1 AND Present = 'Yes', Select Count(*) From
> StaffAttendance WHERE Session = 1 AND Present = 'Yes', '0', 'Attempt
> to start program', 'Due to the lack of completed paperwork between
> RRRR and SITE, we were unable to complete the program as schedule. We
> informed the consumers of the situation.', 'Understandably, the
> consumers were upset. The Dean (SR) was kind enough to try and help
> explain to our members that there was a communications problem that
> caused the paperwork not to be completely in place before we started
> the program. LL and I got the members' phone numbers so that we can
> advise them if we are canceled next week. One consumer, DSL, was being
> uncooperative with us through out the time we were in session. She was
> pouting and having an attitude with all who were there including: the
> parents of another consumer, Dean SR and RRRR staff. I attempted to
> calm Ms. L down and stated that I was informed by MR that there might
> be a problem at 6:45PM on Friday. After the close of business hours on
> Friday. Ms. L stated on many occasions that M should of told her that
> we were not having class today. She called a couple of persons
> including a counselor at ZZZ, to which I was able to speak and explain
> the situation. I attempted to explain the entire situation and that we
> did not definitely know that they were not going to let us in to the
> room until today. The counselor seemed to be understanding. I told her
> that we are expecting to run the program next week and that we would
> contact Ms. L if we are told that we can not.', 'Everyone (Consumers,
> Parents and Staff) wishes to have the program by next week please help
> us get up and running.', 'Attempt to start up the program next
> session.', 'GMH, PgmLdr' );
> 
> 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 Count(*) From Attendance WHERE Session = 1,
>    Select Cou
> 
> [/QUOTE PHPMYADMIN RESULTS]
> 


-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
   __  ___     ___ ____  __
  /  |/  /_ __/ __/ __ \/ /    Gleb Paharenko
 / /|_/ / // /\ \/ /_/ / /__   Gleb.Paharenko@stripped
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.NET
       <___/   www.mysql.com



Thread
Problem with an insert queryGH13 Nov
  • Re: Problem with an insert querycfmaster)13 Nov
  • Re: Problem with an insert queryGleb Paharenko15 Nov