Update SQL Server

Question

pretty simple query for u guys, im just new to this. here's the scenario - one table with 10 fields. On first aspx page, i do insert first 5 fields (programid is one of them). On the second aspx page, i update the same record entered in previous screen (same programid) for the next 5 fields. but the programid is not unique. one programid can have multiple records. I have an ID field which is unique and identity increment.

I need the query which can take the latest ID and the programid and then update only that record and not all records

e.g

update table a 
set field 6='abc',... 
where programid = @programid AND 
      ID = (select id from tablea where programid = @programid order by id desc)

I hope you get the idea

thanks in advance

Answer 1

UPDATE table SET
    X = x, .......
WHERE programId = @programId
AND id = (
    SELECT MAX(id)
    FROM table
    WHERE programId = @programId
)

Answer 2

You can have your first query return the ID of each insert.
I usually do this by putting an output parameter called @ID (or similar) in the stored procedure. this is the basic outline:

ALTER PROCEDURE [dbo].[usp_YourInsertProcedure]
(
    @ProgramID INT, other params... ...        
    @ID INT = NULL OUTPUT
) AS BEGIN
    SET NOCOUNT ON;

    begin try
       INSERT INTO .....
    end try
    begin catch  end catch --nothing to do in catch block..
    set @id = coalesce(scope_identity(), -1) --gets the id of the inserted row
END

In your application code, grab the @id after calling the insert procedure. Then you can then pass that to the second aspx page to use in your update statement. If you get a -1 for the ID, that indicates the insert failed.

Instead of using an output parameter, you could use a return value, though return values are usually used to report success or a failure code, and using them that way may be seen as 'hacky'.

See Scope_Identity, it's a very useful t-sql function.

Answer 3

UPDATE tablea
SET field6 = @valueForField6, ...
WHERE programId = @programId
AND id = 
(
  SELECT TOP 1 id
  FROM tablea
  WHERE programId = @programId
  ORDER BY id DESC
)

This will accomplish what you say you are trying to do - but I think you might have a bit of a problem if you are trying to update the entry added by the previous page, and not just the last added record.

What if more than one user attempts to add entries into this table using the same programId?

I might not be on the correct track here, but you could do something like this:

INSERT INTO tablea (programId, field1, field2, field3, field4, field5)
OUTPUT INSERTED.Id
VALUES(@programId, @valueForField1, @valueForField2, @valueForField3, @valueForField4, @valueForField5)

You can then pass that returned ID to the next page (e.g. if you are using asp.net you could change the action on a button, or you could pass it around in the Session variable).

Then when you go to update, you can just update using the direct Id, e.g.

UPDATE tablea
SET field6 = @valueForField6
WHERE id = @valuePassedThroughToNextPage