Using Variables & Updating tbl: Update STATUS field from CASE exp

SQL Apprentice Question
I need help.
I am fairly new in declaring variables & can be put into one SP.

--> Update the [Status] aliased as OLD to the [NEW] value pulled from a CASE
statement.


[MyTable] table to update
[NEW] (PULLS NEW VALUES BY USING CASE EXPRESSIONS)
[OLD] (VALUE IN MYTABLE NEEDING UPDATING)
[Expire Date] Column in MyTable


-- This is what I have so far.. :
SELECT Status AS OLD, (CASE WHEN [Expire Date] < GETDATE() THEN
'Expired' ELSE CASE WHEN [Expire Date] >= getdate()
THEN 'Active' ELSE 'undefined' END END) AS NEW
FROM MyTable
WHERE (Status <> (CASE WHEN [Expire Date] < GETDATE() THEN 'Expired'
ELSE CASE WHEN [Expire Date] >= getdate()
THEN 'Active' ELSE 'UNK' END END))


I want to
-have one SP to declare these as variables
-Then update the table to something like this:


UPDATE MyTable
Set @Old = @New


---
CREATE TABLE MyTable(
[ID] [int] IDENTITY(1,1)
[Status] [char](10),
[Expire Date] [datetime])


Celko Answers


is this what you meant to post so as to have a valid name, with proper
data element names?

CREATE TABLE Items
(item_id INTEGER NOT NULL PRIMARY KEY,
expiry_date DATETIME NOT NULL,
etc.);


Unlike a deck of punch cards, we can use a VIEW (or a computed column
if you really want to be proprietary instead of portable and
maintainable):


CREATE VIEW ItemStatus(item_id, .., item_status)
AS
SELECT item_id, ..,
CASE WHEN expiry_date < CURRENT_TIMESTAMP
THEN 'expired' ELSE 'active' END
FROM Items;


Your syntax for the CASE expression was wrong. The 'unk' path will not
be executed unless you have NULLs, which I assume that you do not.


You are approaching this problem as if you were using punch cards that
have to PHYSICALLY store the data. Your "old" and "new" (reserved
words in SQL!) look like tapes in a 1950's file merge program.


Start thinking LOGICALLY, with predicates and not procedures. Start
using Standard SQL -- SQL Server has most of SQL-92 these days;
CURRENT_TIMESTAMP and not the old UNIX-based getdate(). Read ISO-11179
and learn how to name data elements.


Reply