How to update column values which is resulted in select statement-CodePudding

I'm trying to find the even or odd numbers from given values, the thing is I want to update the E_and_o column which is resulted in select statement. can someone help me please.

Don't know where I'm doing it wrong.

Create table EvenRodd(
Nrow INT,
E_and_O VARCHAR(15));


///Select statement//
Select nrow, E_and_O,
Case
When Mod(nrow, 2) != 0 then 'Odd'
When Mod(nrow, 2) = 0  then 'Even'
end
From EvenRodd;

//Update //////
Update evenrodd set e_and_o = (Select evenrodd.nrow,
Case
When Mod(evenrodd.nrow, 2) != 0 then 'Odd'
When Mod(evenrodd.nrow, 2) = 0  then 'Even'
end
From EvenRodd) WHERE e_and_o IS NULL;

CodePudding user response：

Update evenrodd set e_and_o = 
Case
When Mod(nrow, 2) != 0 then 'Odd'
When Mod(nrow, 2) = 0  then 'Even'
end

EDIT: if you want to set e_and_o as computed column, and not update it every time:

ALTER TABLE evenrodd DROP COLUMN e_and_o;
ALTER TABLE evenrodd ADD e_and_o AS  
Case
When Mod(nrow, 2) != 0 then 'Odd'
When Mod(nrow, 2) = 0  then 'Even'
end;

CodePudding user response：

I would bet that you pass too many values out from inner select query. Try something like this:

/Update //////

Update evenrodd set e_and_o = (Select 
Case
    When Mod(evenrodd.nrow, 2) != 0 then 'Odd'
    When Mod(evenrodd.nrow, 2) = 0  then 'Even'
end
From EvenRodd) WHERE e_and_o IS NULL;