Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Temp Table and stored procedure issues...

Posted on 2006-03-22
16
Medium Priority
?
316 Views
Last Modified: 2007-12-19
Hi,

I'm a recent convert from MS SQL so I'm still trying to get used to differences in syntax, etc with MySQL.  As such, I'm at the point now where I need to start using Temporary tables in stored procedures.  I've tried a number of different combinations, but I can't get multiple selects into one temporary table.  The following code simply doesn't execute.  Also, can I

First the code then the error messages:

create procedure spAlert()

create temporary table tempAlert(MeasurementShort varchar(30),Sensor varchar(20),SensorData float,SensorSeq int,
                                  SensorDataLastUpdateDate datetime,MinValue float,MaxValue float,EventType varchar(20),
                                  EventDescription varchar(50), EventLastUpdateDate datetime)

select  MeasurementShort,Sensor,SensorData,Sensor.SensorSeq,SensorData.LastUpdateDate as SensorDataLastUpdateDate,MinValue,MaxValue
into    tempAlert
from    SensorData,Sensor,Measurement,user,userpreferences
where  SensorData.SensorSeq = Sensor.SensorSeq AND
           SensorData.MeasurementSeq = Measurement.MeasurementSeq AND
           Measurement.Measurement NOT IN ('SampleDate','SampleTime') AND
           (SensorData.LastUpdateDate > '2006-03-20 10:06:30' AND SensorData.LastUpdateDate < '2006-03-21 10:06:30') AND
           measurement.measurementseq = userpreferences.measurementseq AND user.userseq = userpreferences.userseq AND
           user.username = 'jricha' AND (SensorData.SensorData < userpreferences.MinValue OR
           SensorData.SensorData > userpreferences.MaxValue)

update tempAlert
set EventType = 'Warning'

insert into tempAlert (EventType,EventDescription,EventLastUpdateDate)
select EventType,Description,LastUpdateDate
from Event
where (Event.LastUpdateDate > '2006-03-20 10:06:30' AND Event.LastUpdateDate < '2006-03-21 10:06:30')

select * from tempAlert

Errors:
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 'into      new_alert
from    SensorData,Sensor,Measurement,user,userpreferences
' at line 6

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 'create temporary table tempAlert(MeasurementShort varchar(30),Sensor varchar(20)' at line 1
0
Comment
Question by:blue44
  • 8
  • 8
16 Comments
 
LVL 30

Expert Comment

by:todd_farmer
ID: 16262450
Try changing it to use INSERT INTO ... SELECT syntax.
0
 
LVL 30

Expert Comment

by:todd_farmer
ID: 16262465
So....

INSERT INTO tempAlert SELECT
select  MeasurementShort,Sensor,SensorData,Sensor.SensorSeq,SensorData.LastUpdateDate as SensorDataLastUpdateDate,MinValue,MaxValue
FROM   SensorData,Sensor,Measurement,user,userpreferences
WHERE  SensorData.SensorSeq = Sensor.SensorSeq AND
           SensorData.MeasurementSeq = Measurement.MeasurementSeq AND
           Measurement.Measurement NOT IN ('SampleDate','SampleTime') AND
           (SensorData.LastUpdateDate > '2006-03-20 10:06:30' AND SensorData.LastUpdateDate < '2006-03-21 10:06:30') AND
           measurement.measurementseq = userpreferences.measurementseq AND user.userseq = userpreferences.userseq AND
           user.username = 'jricha' AND (SensorData.SensorData < userpreferences.MinValue OR
           SensorData.SensorData > userpreferences.MaxValue)
0
 

Author Comment

by:blue44
ID: 16262729
Thanks...now I get these error messages:

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 'into      new_alert
from    SensorData,Sensor,Measurement,user,userpreferences
' at line 6

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 'create procedure spAlert()

create temporary table tempAlert(MeasurementShort ' at line 1
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
LVL 30

Expert Comment

by:todd_farmer
ID: 16262786
Sorry, I had a duplicate SELECT keyword in my last post.  Also make sure you are using the semi-colon:

INSERT INTO tempAlert
SELECT  MeasurementShort,Sensor,SensorData,Sensor.SensorSeq,SensorData.LastUpdateDate as SensorDataLastUpdateDate,MinValue,MaxValue
FROM   SensorData,Sensor,Measurement,user,userpreferences
WHERE  SensorData.SensorSeq = Sensor.SensorSeq AND
           SensorData.MeasurementSeq = Measurement.MeasurementSeq AND
           Measurement.Measurement NOT IN ('SampleDate','SampleTime') AND
           (SensorData.LastUpdateDate > '2006-03-20 10:06:30' AND SensorData.LastUpdateDate < '2006-03-21 10:06:30') AND
           measurement.measurementseq = userpreferences.measurementseq AND user.userseq = userpreferences.userseq AND
           user.username = 'jricha' AND (SensorData.SensorData < userpreferences.MinValue OR
           SensorData.SensorData > userpreferences.MaxValue);

0
 

Author Comment

by:blue44
ID: 16262889
Thanks...new error message:

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 'INSERT INTO tempAlert
SELECT  MeasurementShort,Sensor,SensorData,Sensor.SensorS' at line 7

Here's my full code again in case I'm missing any other semi-colons or the like:

create procedure spAlert()

create temporary table tempAlert(MeasurementShort varchar(30),Sensor varchar(20),SensorData float,SensorSeq int,
                                  SensorDataLastUpdateDate datetime,MinValue float,MaxValue float,EventType varchar(20),
                                  EventDescription varchar(50), EventLastUpdateDate datetime)

INSERT INTO tempAlert
SELECT  MeasurementShort,Sensor,SensorData,Sensor.SensorSeq,SensorData.LastUpdateDate as SensorDataLastUpdateDate,MinValue,MaxValue
FROM   SensorData,Sensor,Measurement,user,userpreferences
WHERE  SensorData.SensorSeq = Sensor.SensorSeq AND
           SensorData.MeasurementSeq = Measurement.MeasurementSeq AND
           Measurement.Measurement NOT IN ('SampleDate','SampleTime') AND
           (SensorData.LastUpdateDate > '2006-03-20 10:06:30' AND SensorData.LastUpdateDate < '2006-03-21 10:06:30') AND
           measurement.measurementseq = userpreferences.measurementseq AND user.userseq = userpreferences.userseq AND
           user.username = 'jricha' AND (SensorData.SensorData < userpreferences.MinValue OR
           SensorData.SensorData > userpreferences.MaxValue);

update tempAlert
set EventType = 'Warning'

insert into tempAlert (EventType,EventDescription,EventLastUpdateDate)
select EventType,Description,LastUpdateDate
from Event
where (Event.LastUpdateDate > '2006-03-20 10:06:30' AND Event.LastUpdateDate < '2006-03-21 10:06:30')

select * from tempAlert
0
 
LVL 30

Accepted Solution

by:
todd_farmer earned 2000 total points
ID: 16262929
Try this:

SET DELIMITER ||
create procedure spAlert()
BEGIN
create temporary table tempAlert(MeasurementShort varchar(30),Sensor varchar(20),SensorData float,SensorSeq int,
                                  SensorDataLastUpdateDate datetime,MinValue float,MaxValue float,EventType varchar(20),
                                  EventDescription varchar(50), EventLastUpdateDate datetime);

INSERT INTO tempAlert
SELECT  MeasurementShort,Sensor,SensorData,Sensor.SensorSeq,SensorData.LastUpdateDate as SensorDataLastUpdateDate,MinValue,MaxValue
FROM   SensorData,Sensor,Measurement,user,userpreferences
WHERE  SensorData.SensorSeq = Sensor.SensorSeq AND
           SensorData.MeasurementSeq = Measurement.MeasurementSeq AND
           Measurement.Measurement NOT IN ('SampleDate','SampleTime') AND
           (SensorData.LastUpdateDate > '2006-03-20 10:06:30' AND SensorData.LastUpdateDate < '2006-03-21 10:06:30') AND
           measurement.measurementseq = userpreferences.measurementseq AND user.userseq = userpreferences.userseq AND
           user.username = 'jricha' AND (SensorData.SensorData < userpreferences.MinValue OR
           SensorData.SensorData > userpreferences.MaxValue);

update tempAlert
set EventType = 'Warning';

insert into tempAlert (EventType,EventDescription,EventLastUpdateDate)
select EventType,Description,LastUpdateDate
from Event
where (Event.LastUpdateDate > '2006-03-20 10:06:30' AND Event.LastUpdateDate < '2006-03-21 10:06:30');

select * from tempAlert;
END
||

DELIMITER ;
0
 

Author Comment

by:blue44
ID: 16262980
Don't hate me, but here's another error code...

Unknown system variable 'DELIMITER'
0
 
LVL 30

Expert Comment

by:todd_farmer
ID: 16263013
Don't hate you!  I'm the one giving bad syntax!  LOL!

The very first line should be:

DELIMITER ||

instead of:

SET DELIMITER ||
0
 

Author Comment

by:blue44
ID: 16263365
cool...that created the sp but after running the sp, I got the following error:

Column count doesn't match value count at row 1

So, I went to run the query by itself, but the Query browser doesn't let me execute all statements at once...so it breaks each statement up and gives a corresponding error message:

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 'create temporary table tempAlert(MeasurementShort varchar(30),Sensor varchar(20)' at line 2

Here's my entire query:
BEGIN
create temporary table tempAlert(MeasurementShort varchar(30),Sensor varchar(20),SensorData float,SensorSeq int,
                                  SensorDataLastUpdateDate datetime,MinValue float,MaxValue float,EventType varchar(20),
                                  EventDescription varchar(50), EventLastUpdateDate datetime);

INSERT INTO tempAlert
SELECT  MeasurementShort,Sensor,SensorData,Sensor.SensorSeq,SensorData.LastUpdateDate as SensorDataLastUpdateDate,MinValue,MaxValue
FROM   SensorData,Sensor,Measurement,user,userpreferences
WHERE  SensorData.SensorSeq = Sensor.SensorSeq AND
           SensorData.MeasurementSeq = Measurement.MeasurementSeq AND
           Measurement.Measurement NOT IN ('SampleDate','SampleTime') AND
           (SensorData.LastUpdateDate > '2006-03-20 10:06:30' AND SensorData.LastUpdateDate < '2006-03-21 10:06:30') AND
           measurement.measurementseq = userpreferences.measurementseq AND user.userseq = userpreferences.userseq AND
           user.username = 'jricha' AND (SensorData.SensorData < userpreferences.MinValue OR
           SensorData.SensorData > userpreferences.MaxValue);

update tempAlert
set EventType = 'Warning';

insert into tempAlert (EventType,EventDescription,EventLastUpdateDate)
select EventType,Description,LastUpdateDate
from Event
where (Event.LastUpdateDate > '2006-03-20 10:06:30' AND Event.LastUpdateDate < '2006-03-21 10:06:30');

select * from tempAlert;
END



0
 
LVL 30

Expert Comment

by:todd_farmer
ID: 16263710
Skip the BEGIN and END statements when trying to execute the SQL statements directly (not in the context of the stored procedure definition).
0
 

Author Comment

by:blue44
ID: 16264106
OK...that worked, but I still have a problem with the SQL...it gives me the following error message: "Column count doesn't match value count at row 1" when executing the following portion:

INSERT INTO tempAlert
SELECT  MeasurementShort,Sensor,SensorData,Sensor.SensorSeq,SensorData.LastUpdateDate as SensorDataLastUpdateDate,MinValue,MaxValue
FROM   SensorData,Sensor,Measurement,user,userpreferences
WHERE  SensorData.SensorSeq = Sensor.SensorSeq AND
           SensorData.MeasurementSeq = Measurement.MeasurementSeq AND
           Measurement.Measurement NOT IN ('SampleDate','SampleTime') AND
           (SensorData.LastUpdateDate > '2006-03-20 10:06:30' AND SensorData.LastUpdateDate < '2006-03-21 10:06:30') AND
           measurement.measurementseq = userpreferences.measurementseq AND user.userseq = userpreferences.userseq AND
           user.username = 'jricha' AND (SensorData.SensorData < userpreferences.MinValue OR
           SensorData.SensorData > userpreferences.MaxValue);

I believe the reason for this is that my temp table definition specifies 10 columns and this select only populates 7 columns.  The remaining 3 columns are updated in the next statement.  Do you know how I can get around this?

I've increased the point total to 500 in hope you'll see me through this process.  Many thanks!!
0
 

Author Comment

by:blue44
ID: 16272582
Hi...Are you still interested in helping me with this issue? If not, I'll award you some points and re-post the question.  Please let me know ASAP.  Don't mean to pressure you, but I'm under a deadline.

Many thanks!
0
 
LVL 30

Expert Comment

by:todd_farmer
ID: 16272672
Yes - thanks for bringing this to my attention.  See if this works for you:

BEGIN
create temporary table tempAlert(MeasurementShort varchar(30),Sensor varchar(20),SensorData float,SensorSeq int,
                                  SensorDataLastUpdateDate datetime,MinValue float,MaxValue float,EventType varchar(20),
                                  EventDescription varchar(50), EventLastUpdateDate datetime);

INSERT INTO tempAlert (MeasureMentShort, Sensor, SensorData, SensorSeq, SensorDataLastUpdateDate, MinValue, MaxValue)
SELECT  MeasurementShort,Sensor,SensorData,Sensor.SensorSeq,SensorData.LastUpdateDate as SensorDataLastUpdateDate,MinValue,MaxValue
FROM   SensorData,Sensor,Measurement,user,userpreferences
WHERE  SensorData.SensorSeq = Sensor.SensorSeq AND
           SensorData.MeasurementSeq = Measurement.MeasurementSeq AND
           Measurement.Measurement NOT IN ('SampleDate','SampleTime') AND
           (SensorData.LastUpdateDate > '2006-03-20 10:06:30' AND SensorData.LastUpdateDate < '2006-03-21 10:06:30') AND
           measurement.measurementseq = userpreferences.measurementseq AND user.userseq = userpreferences.userseq AND
           user.username = 'jricha' AND (SensorData.SensorData < userpreferences.MinValue OR
           SensorData.SensorData > userpreferences.MaxValue);

update tempAlert
set EventType = 'Warning';

insert into tempAlert (EventType,EventDescription,EventLastUpdateDate)
select EventType,Description,LastUpdateDate
from Event
where (Event.LastUpdateDate > '2006-03-20 10:06:30' AND Event.LastUpdateDate < '2006-03-21 10:06:30');

select * from tempAlert;
END


Note also that I'm not sure what you are intending to do with the last INSERT statement.  This will create new rows in the tempAlert table rather than update the existing rows.  Is this what you mean to do?
0
 

Author Comment

by:blue44
ID: 16276422
Thanks for getting back to me, Todd!!! I ended up figuring out a solution by adding columns to the temp table before making the second insert.  In any case, I'm going to reward 400 points for helping me get the rest of the important sections working.  I'm sure I will have more questions in the future and hopefully you'll be around to help again.

Darius
0
 

Author Comment

by:blue44
ID: 16276430
Ooops...500 points...my mistake...no worries...:-)
0
 
LVL 30

Expert Comment

by:todd_farmer
ID: 16277566
Good deal - thanks!
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
In this blog, we’ll look at how improvements to Percona XtraDB Cluster improved IST performance.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses
Course of the Month13 days, 12 hours left to enroll

581 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question