CODEWITHSUNDEEP
sqlsql-servert-sqlstored-procedures
guyh92
guyh92

Reputation: 383

Creating a stored procedure to update a row in a SQL database using data from the previous row

Good afternoon all,

I have a database which is used to track production on an assembly line.

Effectively the finished product will show each stage of the production line and the serial number of what is currently in it.

I have created a stored procedure to retrieve and display this data, but where I am struggling is to move a machine from stage 1 to stage 2 and stage 2 to stage 3 etc... The table is very simple, it contains the following rows:

Stage_ID (PK, int, not null)
Build_ID(FK, int, null)

Effectively, I want to build a stored procedure to move the value of Build_ID where Stage_ID = 1 to Stage_ID = 2.

Any idea how I would do this?

Thanks

Upvotes: 0

Views: 6240

Answers (4)

Tim
Tim

Reputation: 8919

Keys are not mutable, so you would have to insert the new row and delete the old row. Not an ideal design you have there.

P.S. If StageID is the PK as you have it, you can have only one row where StageID = 2:

 Stage_ID (PK, int, not null)   <<<---
 Build_ID(FK, int, null)

What you want is a table like this:

  create table process
  (id int primary key,
   buildid int not null,
   stage  int not null,
   entrydatetime datetime,
   exitdatetime datetime
   )

showing when a build enters and leaves a processing stage. Or you could derive the time the stage was exited by assuming the entry time of the next stage if the stage values are sortable sequentially in a meaningful way.

In any event, you don't want to make a changing value like "stage in the process" be a PK. Stage is a changing value, a property or attribute of a build, whereas a PK is an immutable entity identifier.

Upvotes: 0

jhinkley
jhinkley

Reputation: 668

update stageTable
set build_id = (select build_id from stageTable where stage_id = 1)
where stage_id = 2

if you need it as a procedure you could do something like this

create procedure sp_moveStage(@from int, @to int)
as 

update stageTable
set build_id = (select build_id from stageTable where stage_id = @from)
where stage_id = @to

and execute it with 

exec sp_moveStage 1, 2

Upvotes: 2

Jodrell
Jodrell

Reputation: 35716

erm,

DECLARE @Build_ID int;

BEGIN TRANSACTION;

SELECT @Build_ID = Build_ID FROM SomeTable WHERE Stage_ID = 1;

UPDATE SomeTable SET Build_ID = @Build_ID WHERE Stage_ID = 2;

COMMIT TRANSACTION;

Upvotes: 0

Dan Bracuk
Dan Bracuk

Reputation: 20804

Something like this might work.
Step 1 - get the max stage id for your build id and set it to a variable. Step 2 - add a record to the table. The stage id will be the variable from Step 1, plus 1.

Upvotes: 0

Related Questions