Insert command with Select Max

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Insert command with Select Max

Author

Topic

DaveBF
Yak Posting Veteran

89 Posts

Posted - 2013-10-17 : 09:35:18

I've been trying to figure this out myself and haven't been able to.

I have a table with a column called SortOrder. It's an integer. The table also has a name and a city.
I want to do an Insert and fill in SortOrder with the next higher integer, of all rows with city='NY'

Insert (name,city,SortOrder) values ('Dave','NY',
select max(SortOrder)+1 from myTable where city='NY')

I hope that's clear. Thanks.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-10-17 : 09:57:07

Do it like this:

INSERT INTO myTable (name, city, SortOrder)
SELECT 'Dave','NY', ISNULL(MAX(SortOrder),0) +1 FROM myTable WHERE city = 'NY';

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-17 : 10:02:10

of all rows with city='NY'
do you mean you've multiple rows for city='NY'?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

DaveBF
Yak Posting Veteran

89 Posts

Posted - 2013-10-17 : 10:06:51

quote:

Originally posted by James K

Do it like this:

INSERT INTO myTable (name, city, SortOrder)
SELECT 'Dave','NY', ISNULL(MAX(SortOrder),0) +1 FROM myTable WHERE city = 'NY';

Thank you!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources