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