rain13 Posted August 13, 2013 Share Posted August 13, 2013 Hello. Could anyone tell me how I coud use MAX(...) with insert into? DECLARE max_id INTEGER;SET max_id = (SELECT MAX( MsgID ) FROM privmsg);INSERT INTO `g`.`privmsg` (ID, MsgID, Data) VALUES (NULL, max_id , 'data'); says#1064 - 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 'DECLARE max_id INTEGER' at line 1 I have also tried INSERT INTO `g`.`privmsg` (ID, MsgID, Data) VALUES (NULL,SELECT 1 + coalesce((SELECT max(MsgID) FROM privmsg), 0) , 'data'); which is based on this example: http://stackoverflow.com/questions/1587562/problem-with-mysql-insert-max1#1064 - 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 1 + coalesce((SELECT max(MsgID) FROM privmsg), 0) , 'data')' at line 1Of course I could write PHP gode that first gets MAX and then uses that value, but I think it would run faster if it were in single query. Link to comment Share on other sites More sharing options...
justsomeguy Posted August 13, 2013 Share Posted August 13, 2013 In the first piece of code, the problem is not the use of MAX. The problem is the first line, that's exactly what the error message says. Maybe there's a line before that with a problem, or maybe DECLARE isn't the right keyword to use (I haven't looked it up). In the second piece of code, you cannot use a select query inside the value list in an insert. You can use a select query as the entire value list, though. INSERT INTO `g`.`privmsg` (ID, MsgID, Data) SELECT NULL as ID, 1 + coalesce((SELECT max(MsgID) FROM privmsg), 0) as MsgID , 'data' as Data; 1 Link to comment Share on other sites More sharing options...
rain13 Posted August 13, 2013 Author Share Posted August 13, 2013 Thanks for fast and useful answer. Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now