troubleshooting Question

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

Avatar of ullenulle
ullenulleFlag for United States of America asked on
ASPMySQL Server
10 Comments1 Solution623 ViewsLast Modified:
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
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 10 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 10 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros