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

ullenulle
ullenulle used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2012

Commented:
Can you post the complete error message?
Top Expert 2012

Commented:
Try this:

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

Author

Commented:
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
Acronis in Gartner 2019 MQ for datacenter backup

It is an honor to be featured in Gartner 2019 Magic Quadrant for Datacenter Backup and Recovery Solutions. Gartner’s MQ sets a high standard and earning a place on their grid is a great affirmation that Acronis is delivering on our mission to protect all data, apps, and systems.

Top Expert 2012
Commented:
Sorry it should be

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

(remove the "VALUES")
Top Expert 2012

Commented:
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 =)

Author

Commented:
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
Top Expert 2012

Commented:
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

Author

Commented:
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

Author

Commented:
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. ;-)
Top Expert 2012

Commented:
=) 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!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial