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

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

ASKER CERTIFIED SOLUTION

johanntagle

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

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

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!