Avatar of ullenulle
ullenulle
Flag for United States of America asked on

Use a query in an INSERT statement in MySQL...

Hey MySQL-experts. :-)

I have a problem. I have an INSERT statement in MySQL, and one of the values in my statement must be generated at the same time as the INSERT is executed.
The variable (patient_no) we're talking about is combined from two parts... 5 numbers all in all:

XXYYY

XX = a cookie value that I retrieve... no problem there
YYY = the highest value of the LAST 3 DIGITS from existing patient_no PLUS 1... that is 1 higher than the highest value of YYY in the table so far. As an little extra, the first value must be 300.

I tried something like this with a subquery:

INSERT INTO tbl_patient_regi (patient_no) VALUES (CONCAT('" & xsite &"', (SELECT MAX(SUBSTRING(patient_no, 3, 3))+1 FROM tbl_patient_regi))

xsite is the cookie-value (XX).

When I try to execute the INSERT, I get a syntax-error. :-(  So what am I doing wrong, and how should I do it to get, what I want?

Best regards

Ullenulle
MySQL ServerASP

Avatar of undefined
Last Comment
johanntagle

8/22/2022 - Mon
johanntagle

Can you post the complete error message?
johanntagle

Try this:

INSERT INTO tbl_patient_regi (patient_no) VALUES
select CONCAT('" & xsite &"',  MAX(SUBSTRING(patient_no, 3, 3))+1)
FROM tbl_patient_regi
ullenulle

ASKER
Here's the error (same with johanntable's suggestion):

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[MySQL][ODBC 5.1 Driver][mysqld-5.0.51b-community-nt]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 CONCAT('41', (MAX(SUBSTRING(patient_no, 3, 3))+1 FROM tbl_patient_regi),'' at line 1
Your help has saved me hundreds of hours of internet surfing.
fblack61
ASKER CERTIFIED SOLUTION
johanntagle

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
johanntagle

Looks like in your original query you really only lacked one closing parenthesis

INSERT INTO tbl_patient_regi (patient_no) VALUES
(CONCAT('" & xsite &"', (SELECT MAX(SUBSTRING(patient_no, 3, 3))+1 FROM tbl_patient_regi)))

But I like my version better =)
ullenulle

ASKER
Ooopps... embarracing... but now I get this error:

Microsoft OLE DB Provider for ODBC Drivers error '80004005'

[MySQL][ODBC 5.1 Driver][mysqld-5.0.51b-community-nt]You can't specify target table 'tbl_patient_regi' for update in FROM clause

Obviously MySQL doesn't like to mix it all up the way I do... Is there another option?
I also tried your suggestion to move "SELECT" out of the parenthesis, but then I get this error:

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[MySQL][ODBC 5.1 Driver][mysqld-5.0.51b-community-nt]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 'FROM tbl_patient_regi)),'','','','','na','na','0','0','0','0','0','0','0','0','0' at line 1

 I usually build up my SQL statements like this:

Dim var1, var 2......
Dim sConnString, objConn, sSQL, sSQL1, sSQL2.......
var1 = Request.Cookies("bla bla")
var2 = Request.Form("variable2")
var3 = ......
sConnString = MM_conn_STRING
Set objConn = Server.CreateObject ("ADODB.Connection")
objConn.Open(sConnString)
sSQL = INSERT INTO tbl_patient_regi (patient_no) VALUES
(CONCAT('" & xsite &"', (SELECT MAX(SUBSTRING(patient_no, 3, 3))+1 FROM tbl_patient_regi)))
sSQL1 = INSERT INTO tbl_patient_regi_log (patient_no) VALUES
(CONCAT('" & xsite &"', (SELECT MAX(SUBSTRING(patient_no, 3, 3)) FROM tbl_patient_regi)))
objConn.execute(sSQL)
objConn.execute(sSQL1)
objConn.Close
Set objConn = nothing

I thought if I can run the query in a sSQL-statement before my INSERT-statement and save that query-result temporary to use in the INSERT-statement? That way I wouldn't have to mix up the INSERT and SELECT in one statement... I just don't know how to save a temporary query-value...

Best regards

Ullenulle
johanntagle

Missed the fact that you are inserting to the same table.  Use my version of the SQL.  See below - I tried a modified version of both our queries on one of my test tables, and mine works =) :

mysql> select * from Error;
+----------+---------+---------------------+
| ErrorGID | Message | Created             |
+----------+---------+---------------------+
|        5 | ab      | 2012-05-23 14:34:34 |
+----------+---------+---------------------+
1 row in set (0.02 sec)

mysql> insert into Error (Message) values (CONCAT('" & xsite &"', (SELECT MAX(SUBSTRING(Message, 3, 3))+1 from Error) ));
ERROR 1093 (HY000): You can't specify target table 'Error' for update in FROM clause
mysql> insert into Error (Message) select CONCAT('" & xsite &"', MAX(SUBSTRING(Message, 3, 3))+1) from Error;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> select * from Error;
+----------+---------------+---------------------+
| ErrorGID | Message       | Created             |
+----------+---------------+---------------------+
|        5 | ab            | 2012-05-23 14:34:34 |
|        7 | " & xsite &"1 | 2012-05-23 16:34:17 |
+----------+---------------+---------------------+
2 rows in set (0.00 sec)

Open in new window

Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ullenulle

ASKER
No no no... I didn't try your suggestion well enough... After looking up some stuff on Google, I managed to use your suggestion for real (I have a bunch of other variables in my statement)... so you were right! You had a reason to like your suggestion better than mine. ;-)  Thank you BIG TIME!

Best regards

Ulllenulle
ullenulle

ASKER
Thank you again. Good I just got a helmet for biking, because I want to hit myself when I can't figure out to do the MySQL correct. ;-)
johanntagle

=) You're welcome.  Even if your your version worked I think you should consider writing SQLs in the "standard" way as much as possible so that people can more easily understand it.  "INSERT INTO tablename (columnnames) SELECT columnnames from tablename" is the most accepted pattern for INSERT where the data to be inserted is to be taken from a table (whether it's a different table or not).  

Another reason is readability - my version had two less pairs of parentheses - I think you will agree that finding matching opening and closing parentheses is such a pain =).

Anyway, glad to help.  Later!
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck