By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
431,805 Members | 1,202 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 431,805 IT Pros & Developers. It's quick & easy.

Does MAX() in INSERT / UPDATE work ?

P: 1
How can I say this ?

Expand|Select|Wrap|Line Numbers
  1. INSERT INTO table SET nr=MAX(nr)+1 WHERE date='xx.xx.xx xx:xx:xx'
nr is not primary, so I can not use auto increment.
Jan 28 '13 #1
Share this Question
Share on Google+
2 Replies

Expert Mod 10K+
P: 12,364
Please use code tags when posting code.

Use an aggregate query to return the max.
Jan 28 '13 #2

P: 185
I believe Rabbit was trying to say nested query.

Your above syntax is an update query (update ... set ..=... where ...). Insert syntax is insert into xxx (columns) values (values).

So, if you are trying to insert use the syntax like this:
Expand|Select|Wrap|Line Numbers
  1.  insert into table (nr) select max(nr) from table where ....
If you are trying to update the existing value, use something like this:
Expand|Select|Wrap|Line Numbers
  1. update table set nr = select max(nr) from ...
In that last case, make sure you enclose the nested query with () making sure that the where applies to the right statement (i.e. update or select).
Jan 29 '13 #3

Post your reply

Sign in to post your reply or Sign up for a free account.