insert query

  • rjstephens
  • Professor
  • Professor
  • User avatar
  • Posts: 774
  • Loc: Brisbane, Australia

Post 3+ Months Ago

This is what I want to do:


Code: [ Select ]
INSERT INTO `members` ( `uid` , `name` , `city` , `state` , `email` , `phone` , `nickname` , `password` , `maillist` )
VALUES ((MAX( `uid` ) + 1), 'a', 'b', 'c', 'd', 'e', 'f', 'g', '0')
  1. INSERT INTO `members` ( `uid` , `name` , `city` , `state` , `email` , `phone` , `nickname` , `password` , `maillist` )
  2. VALUES ((MAX( `uid` ) + 1), 'a', 'b', 'c', 'd', 'e', 'f', 'g', '0')


but it doesn't work :(

What do I have to do to make it work?
  • _Leo_
  • Proficient
  • Proficient
  • User avatar
  • Posts: 279
  • Loc: Buenos Aires, Argentina

Post 3+ Months Ago

Well, if it's MySQL we are talking about, there is a column attribute called auto_increment. Once a column has it, it will get the MAX + 1 value if you don't send it in the insert query.

Other databases implement different solutions for the same. The query you wrote shouldn't be used.
  • rjstephens
  • Professor
  • Professor
  • User avatar
  • Posts: 774
  • Loc: Brisbane, Australia

Post 3+ Months Ago

the qeury I wrote didn't work.

Anyways, thanks for the answer, i'll try that.
  • hiryux
  • Student
  • Student
  • User avatar
  • Posts: 65

Post 3+ Months Ago

If you are using Oracle SQL plus, you can make 'uid' as a surrogate key.

I'm not putting you down, but let me just explain really quick what it is:

get a number. increment by 1 (or w/e number u want to increment it by). that number is unique, so it's a good candidate key. I figured it was kinda what you wanted?

the following code works on Oracle. Don't know if it works in mysql.

nothing gets changed at the create table script, but you'll have to type this command at the sql script:

CREATE SEQUENCE uid
INCREMENT BY 1 START WITH 001; //001 can actually be any number.
this will set uid to be a surrogate key.

what also changes is your insert, I'll use the insert you had:

INSERT INTO `members` ( `uid` , `name` , `city` , `state` , `email` , `phone` , `nickname` , `password` , `maillist` )
VALUES (uid.nextval, 'a', 'b', 'c', 'd', 'e', 'f', 'g', '0') ;

and just keep doing this for as much as needed. uid.nextval does the +1 increment. The first insert is identital to the last one, in terms of uid.nextval. U'll need it all the times.

I don't think aggregate functions work on inserts, but i'm not sure.

Hope it helps =)
  • rjstephens
  • Professor
  • Professor
  • User avatar
  • Posts: 774
  • Loc: Brisbane, Australia

Post 3+ Months Ago

i doubt it will. I am using mysql. Leo's solution worked, though.

Post Information

  • Total Posts in this topic: 5 posts
  • Users browsing this forum: No registered users and 56 guests
  • You cannot post new topics in this forum
  • You cannot reply to topics in this forum
  • You cannot edit your posts in this forum
  • You cannot delete your posts in this forum
  • You cannot post attachments in this forum
 
 

© 1998-2014. Ozzu® is a registered trademark of Unmelted, LLC.