[SOLVED] SQL Get id value and insert into another column

SQL Get id value and insert into another column

I am attempting to create an sql statement that will insert an inital record, retrieve the new id (a autonumber primary key) and insert that into another column in the table to serve as a defualt value.

This is what I have so far:

INSERT INTO users (first_name, last_name, email, password, shot, year, campus, subject,

 qual_level, job_title, blurb, presentions, offers, moderated, version_current,

 version_replace, salt, loggable) VALUES (@sfirst_name, @slast_name, @semail, @spassword,

 @sshot, @syear, @scampus, @ssubject, @squal_level, @sjob_title, @sblurb, @spresentions, 

@soffers, @smoderated, @scurrent_version, @sversion_replace, @ssalt, 1) SELECT 

SCOPE_IDENTITY() as NewId UPDATE users (version_replace) VALUES (NewId) WHERE id = NewId

This isn't working as is and I could use a hand to correct the syntex

Solution

Your update isn't right it should be

UPDATE 
    users 
SET 
    version_replace =  SCOPE_IDENTITY()

WHERE id = SCOPE_IDENTITY()

This is because this statement

SELECT SCOPE_IDENTITY() as NewId

Had no impact on the following statement

If you want you can also use a variable to store the NewID

DECLARE @NewID  int
SET @NewId =  SCOPE_IDENTITY()

UPDATE 
    users 
SET 
   version_replace =  @NewID  
WHERE id =@NewID

If you were doing this with multiple rows you would probably want to look at the OUTPUT Clause