Jump to content

Impossible to use MAX in INSERT ?


rain13

Recommended Posts

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

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;

  • Like 1
Link to comment
Share on other sites

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 account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...