Select Max() not working

[Brian]

I'm trying to run a select max() on a primary key/unique/non-identity
column then + 1, all while running an insert into.. select statement.
For some reason, the select max isn't quite doing the trick as I get a
primary key constraint error (MSG 2627) in SQL server!

It's probably something simple like adding quotes or parenthesis or
something, but I've tried...

Here's my code:

INSERT INTO [frameinventory]
([frameid]
,[framenumber]
,[framename]
,[colornumber]
,[a]
,[dbl]
,[templelength]
,[b]
,[ed]
,[cost]
,[retailprice]
,[upccode]
,[eyesize]
,[bridgesize]
,[groupcost]
,[colordescription]
,[lenscolor]
,[lenscolorcode]
,[circumference]
,[edangle]
,[frontprice]
,[halftemplesprice]
,[templesprice]
,[manufacturername]
,[brandname]
,[collectionname]
,[gendertype]
,[agegroup]
,[activestatus]
,[productgroupname]
,[rimtype]
,[material]
,[frameshape]
,[country]
,[yearintroduced]
,[upccode_type])
select (select max(frameid) + 1 from frameinventory), fpc, stylename,
colorcode, a, dbl, temple, b, ed, completeprice,
((framesdata.completeprice*1600)+5), upc, eye, bridge, completeprice,
colordescription, lenscolor, lenscolorcode, circumference, edangle,
frontprice, halftemplesprice, templesprice, manufacturername,
brandname, collectionname, gendertype, agegroup, 'Active',
productgroupname, rimtype, material, frameshape, country,
yearintroduced, 'UPC'
from framesdata
where not exists (select * from frameinventory where
frameinventory.upccode=framesdata.upc)
THANKS!

[David Portas]

"Brian" <ey****@gmail.comwrote in message
news:76**********************************@k7g2000h sd.googlegroups.com...

I'm trying to run a select max() on a primary key/unique/non-identity
column then + 1, all while running an insert into.. select statement.
For some reason, the select max isn't quite doing the trick as I get a
primary key constraint error (MSG 2627) in SQL server!

It's probably something simple like adding quotes or parenthesis or
something, but I've tried...

Here's my code:

INSERT INTO [frameinventory]
([frameid]
,[framenumber]
,[framename]
,[colornumber]
,[a]
,[dbl]
,[templelength]
,[b]
,[ed]
,[cost]
,[retailprice]
,[upccode]
,[eyesize]
,[bridgesize]
,[groupcost]
,[colordescription]
,[lenscolor]
,[lenscolorcode]
,[circumference]
,[edangle]
,[frontprice]
,[halftemplesprice]
,[templesprice]
,[manufacturername]
,[brandname]
,[collectionname]
,[gendertype]
,[agegroup]
,[activestatus]
,[productgroupname]
,[rimtype]
,[material]
,[frameshape]
,[country]
,[yearintroduced]
,[upccode_type])
select (select max(frameid) + 1 from frameinventory), fpc, stylename,
colorcode, a, dbl, temple, b, ed, completeprice,
((framesdata.completeprice*1600)+5), upc, eye, bridge, completeprice,
colordescription, lenscolor, lenscolorcode, circumference, edangle,
frontprice, halftemplesprice, templesprice, manufacturername,
brandname, collectionname, gendertype, agegroup, 'Active',
productgroupname, rimtype, material, frameshape, country,
yearintroduced, 'UPC'
from framesdata
where not exists (select * from frameinventory where
frameinventory.upccode=framesdata.upc)
THANKS!

Always specify what version of SQL Server you are using. Assuming 2005 or
2008 then use the ROW_NUMBER() function to generate frameid. In earlier
versions, consider using an IDENTITY column.

If your query returns more than one row then every row will get the same
frameid, which probably explains the key violation error.

--
David Portas

[--CELKO--]

On Sep 7, 1:19*am, Brian <[e-mail]:

I'm trying to run a select max() on a primary key/unique/non-identity
column then + 1, all while running an insert into.. select statement.
For some reason, the select max isn't quite doing the trick as I get a
primary key constraint error (MSG 2627) in SQL server!

It's probably something simple like adding quotes or parenthesis or
something, but I've tried...

Here's my code:

INSERT INTO [frameinventory]
* * * * * *([frameid]
* * * * * *,[framenumber]
* * * * * *,[framename]
* * * * * *,[colornumber]
* * * * * *,[a]
* * * * * *,[dbl]
* * * * * *,[templelength]
* * * * * *,[b]
* * * * * *,[ed]
* * * * * *,[cost]
* * * * * *,[retailprice]
* * * * * *,[upccode]
* * * * * *,[eyesize]
* * * * * *,[bridgesize]
* * * * * *,[groupcost]
* * * * * *,[colordescription]
* * * * * *,[lenscolor]
* * * * * *,[lenscolorcode]
* * * * * *,[circumference]
* * * * * *,[edangle]
* * * * * *,[frontprice]
* * * * * *,[halftemplesprice]
* * * * * *,[templesprice]
* * * * * *,[manufacturername]
* * * * * *,[brandname]
* * * * * *,[collectionname]
* * * * * *,[gendertype]
* * * * * *,[agegroup]
* * * * * *,[activestatus]
* * * * * *,[productgroupname]
* * * * * *,[rimtype]
* * * * * *,[material]
* * * * * *,[frameshape]
* * * * * *,[country]
* * * * * *,[yearintroduced]
* * * * * *,[upccode_type])
select (select max(frameid) + 1 from frameinventory), fpc, stylename,
colorcode, a, dbl, temple, b, ed, completeprice,
((framesdata.completeprice*1600)+5), upc, eye, bridge, completeprice,
colordescription, lenscolor, *lenscolorcode, circumference, edangle,
frontprice, halftemplesprice, templesprice, manufacturername,
brandname, collectionname, gendertype, agegroup, 'Active',
productgroupname, rimtype, material, frameshape, country,
yearintroduced, 'UPC'
from framesdata
where not exists (select * from frameinventory where
frameinventory.upccode=framesdata.upc)

THANKS!

Just looking at this, it seems that you don't need frame_id since you
have attributes called frame_number, frame_name and UPC (which you
have given two names!! Fix that; a data element has one and only one
in a schema). I am also curious about what a "gender_type" is because
gender is a property -- you say "<something>_gender" -- and not an
entity. Was it supposed to be a style classification?

[Brian]

On Sep 7, 4:54*am, --CELKO-- <[e-mail]:

On Sep 7, 1:19*am, Brian <[e-mail]:

I'm trying to run a select max() on a primary key/unique/non-identity
column then + 1, all while running an insert into.. select statement.
For some reason, the select max isn't quite doing the trick as I get a
primary key constraint error (MSG 2627) in SQL server!

It's probably something simple like adding quotes or parenthesis or
something, but I've tried...

Here's my code:

INSERT INTO [frameinventory]
* * * * * *([frameid]
* * * * * *,[framenumber]
* * * * * *,[framename]
* * * * * *,[colornumber]
* * * * * *,[a]
* * * * * *,[dbl]
* * * * * *,[templelength]
* * * * * *,[b]
* * * * * *,[ed]
* * * * * *,[cost]
* * * * * *,[retailprice]
* * * * * *,[upccode]
* * * * * *,[eyesize]
* * * * * *,[bridgesize]
* * * * * *,[groupcost]
* * * * * *,[colordescription]
* * * * * *,[lenscolor]
* * * * * *,[lenscolorcode]
* * * * * *,[circumference]
* * * * * *,[edangle]
* * * * * *,[frontprice]
* * * * * *,[halftemplesprice]
* * * * * *,[templesprice]
* * * * * *,[manufacturername]
* * * * * *,[brandname]
* * * * * *,[collectionname]
* * * * * *,[gendertype]
* * * * * *,[agegroup]
* * * * * *,[activestatus]
* * * * * *,[productgroupname]
* * * * * *,[rimtype]
* * * * * *,[material]
* * * * * *,[frameshape]
* * * * * *,[country]
* * * * * *,[yearintroduced]
* * * * * *,[upccode_type])
select (select max(frameid) + 1 from frameinventory), fpc, stylename,
colorcode, a, dbl, temple, b, ed, completeprice,
((framesdata.completeprice*1600)+5), upc, eye, bridge, completeprice,
colordescription, lenscolor, *lenscolorcode, circumference, edangle,
frontprice, halftemplesprice, templesprice, manufacturername,
brandname, collectionname, gendertype, agegroup, 'Active',
productgroupname, rimtype, material, frameshape, country,
yearintroduced, 'UPC'
from framesdata
where not exists (select * from frameinventory where
frameinventory.upccode=framesdata.upc)

THANKS!

Just looking at this, it seems that you don't need frame_id since you
have attributes called frame_number, frame_name and UPC (which you
have given two names!! Fix that; a data element has one and only one
in a schema). *I am also curious about what a "gender_type" is because
gender is a property -- you say "<something>_gender" -- and not an
entity. *Was it supposed to be a style classification?- Hide quoted text -

- Show quoted text -

To clarify from my early AM post,

1. I'm using SQL 2005 Express at the moment.
2. The schema(e) for the two tables were designed by app developers
and I cannot change them.
3. framesdata table is a temporary import table that the application
uses to pull data from a data cd
4. The only primary key for either table is the frameid column in
frameinventory
5. All other data is pre-populated. I'm basically trying to copy
data that does not exist in frameinventory from framesdata
6. Not all information in frameinventory always comes from framesdata
(ie user-input via the app). I suspect that is why the primary key is
not tied to the framesdata data.
7. Frameinventory containes 27000 entries. framesdata contains 88000
entries.
8. The primary key in frameinventory is not the same as the row
number (row 1 has a primary key of 699), and I will double check later
today if they are sequential.

I'll fiddle with row_number() and see where we go with things.

Thanks for the input!!

[--CELKO--]

>2. The schema(e) for the two tables were designed by app developers and I cannot change them. <<

Those are very scary words. Tables should be designed by database
people and not developers. Thatg is how you get sloppy schemas like
this. Might want to find out who your DB person is and get them to
fix things.

>I'll fiddle with row_number() and see where we go with things. <<

You might want to look at the UPC and find out how many different
things you have with the same UPC.

[Brian]

On Sep 7, 7:04*pm, --CELKO-- <[e-mail]:

2. *The schema(e) for the two tables were designed by app developersand I cannot change them. <<

Those are very scary words. *Tables should be designed by database
people and not developers. *Thatg is how you get sloppy schemas like
this. *Might want to find out who your DB person is and get them to
fix things.

The app is a frontend for their table setup - if I change it, the app
dies. I'm simply bypassing an inefficiency in the app by importing
the data myself (whereas they display it all to the screen
simultaneously)

>

I'll fiddle with row_number() and see where we go with things. <<

You might want to look at the UPC and find out how many different
things you have with the same UPC.

Again, I'm stuck with a semi-sequential FrameID primary key. There
are approximately 80k unique UPC's in the table, and it would make
more sense to use them...

[shuurai11]

Those are very scary words. *Tables should be designed by database
people and not developers. *Thatg is how you get sloppy schemas like
this. *Might want to find out who your DB person is and get them to
fix things.

Unfortunately in the real world, a lot of companies use systems that
are designed by developers. Sometimes those developers come up with
schemas that are less than optimal, and in some cases even downright
stupid.

But the reality is, you generally can't just have your DBA fix the
schema without rewriting the application code; and few companies are
going to put the time and resources into reworking their rather
expensive system just because some guy on the internet doesn't like
the schema the vendor came up with, no matter how big that guy's ego
happens to be.

[--CELKO--]

>The app is a front end for their table setup - if I change it, the app dies. *I'm simply bypassing an inefficiency in the app by importing the data myself (whereas they display it all to the screen simultaneously) <<

Since I make some of my living fixing things like this, I tell you
that you are not the Lone Ranger. Modern application developers are
too damn busy being "agile" or "extreme" (aka "Git'er done!" in the
words of Larry the cable guy) that they never bothered with basic
software engineering concepts like coupling and cohesion.

That ignorance always leads to tight coupling, where the DB is used as
if it were a file in their procedural language. Some of the time it
also leads to low cohesion, but the tight coupling then prevents any
chance of a robust program.

Rant, rant, rant. Not that this helps you, but I feel better...

>Again, *I'm stuck with a semi-sequential FrameID primary key. *Thereare approximately 80k unique UPC's in the *table, and it would make moresense to use them... <<

Amen. You would get an interface to the POS system, external
validation and verification, access to industry sales data, etc. But,
hey, the cowboy coders might have to research and think; their screen
might not paint as fast, etc. So let's pretend that the entire world
revolves around painting screens and keeping their code as 1950's as
possible.

Opps, ranting again ..