Folks

I have a table with four fields

PK
Field1
Field2
Status

I'd like to write an UPDATE query (err, stored proc) that set the value
of Status to 1 if field1 < fiel2, to 2 if both fields are equal and to
3 otherwise.

What would be the syntax of such a query ?!

Thanks & regards

--alexT

UPDATE YourTable
SET status =
  CASE
   WHEN col1 < col2 THEN 1
   WHEN col1 = col2 THEN 2
   WHEN col1 > col2 THEN 3
   ELSE NULL
  END ;

However, if Status will always depend on the values of the other two
columns then your table is not in 3NF (you have a transitive
dependency) and you would be better advised to put the Status column in
a view rather than the table.

Try,

update t1
set [status] = case when c1 < c2 then 1 else case when c1 = c2 then 2 else 3
end end

You can implement this using a computed column.

Example:

create table t1 (
pk int not null identity primary key,
c1 int not null,
c2 int not null,
[status] as case when c1 < c2 then 1 else case when c1 = c2 then 2 else 3
end end
)
go

insert into t1(c1, c2 ) values(1, 2)
insert into t1(c1, c2 ) values(1, 1)
insert into t1(c1, c2 ) values(2, 1)
go

select * from t1
go

drop table t1
go

AMB

Show quoteHide quote

update tableName set Status = Case when Field1 < Field2 then 1 when Field1 =
Field2 then 2 else 3 end

I really suggest though that you forget the Status column completely as you
can calculate this any time you want in a query.
Another possibility is to use a calculated column.
In this case, the value of Status will be automatically adjusted any time
there is a modification to Field1 or Field2.

Show quoteHide quote

Here you go.


CREATE TABLE #Temp(col1 int, col2 int, status int)
INSERT INTO #Temp VALUES(5,10,NULL)
INSERT INTO #Temp VALUES(10,5,NULL)
INSERT INTO #Temp VALUES(7,7,NULL)

SELECT * FROM #Temp
UPDATE #Temp
SET Status = CASE WHEN col1 < Col2 Then 1
      WHEN Col2 < col1 Then 3
     ELSE 2 END

SELECT * FROM #Temp


Show quoteHide quote

Eep, are you really going to UPDATE this constantly?  If you have 5000
updates/inserts in a day, are you going to maintain this by following up
with 5000 updates to set the status?  I agree with David, avoid transitive
dependencies.  Show the status when you perform a select, there is
absolutely no reason to store/maintain it.



Show quoteHide quote

Hi everyone,

May thanks for the responses - most usefull !

My case is (obviously) much more complicated: field1 and filed2 (and
there are 5 of them) are actualy results from complex queries and a
real time computation is just killing the server.

My solution is to keep a record by record status and have it updated
every time a record changes. Seems to work fine.

Now I have a follow up question: assuming I'd like to update *all* the
status fields for every record, how should I proceed ? I can not use a
view because it contains agregates (the status computation) and is not
updatable.

I thought about something like

UPDATE MyTable
SET STATUS = <x, where x is from a query returning all status for each
record>

How would you build this ?!

Regards

--alexT

Dangers of update statement
Automating database changes
[OT] SQL Humor
estimate size of table based on number of rows
Track Modification Time