Clever Geek Handbook

SQL Server - setting column value based on another column

I have a 3 column Part1

, Part2

and OnePiece

.

Part1

can be assigned [1,2,3,4,5].

Part2

you can assign [1,2,3,4].

OnePiece

it is of type Bit

.

Both Part1

and Part2

cannot have the same value, and also, if Part1

equal to 5, then Part2

cannot have a value.

What I am trying to do is set OnePiece

to 1 and make sure it Part2

doesn't take a value when Part1

= 5.

How can i do this?

I will clarify my request.

The column OnePiece

is a flag and is calculated automatically and is set to 1 if Part1

= 5, and for Part2

it cannot be set to any value if Part1

= 5.

The reason is that Part1

= 5 means it is a one component product, so there is no Part2

and should not allow any other value.

So, that would do two things that I'm guessing; trigger and check limit. Hope I have offered more details.

+3


source to share


3 answers


Here's a check constraint to disallow values ​​in Part2

when OnePiece

equal to 1 and Part5

equal to 5:



alter table YourTable add constraint CHK_YourTable
    check (OnePiece <> 1 or Part1 <> 5 or Part2 is null)
+1


source


You can add a check constraint to verify this:



ALTER TABLE my_table
ADD CONSTRAINT my_table_chk
CHECK (part1 <> part2 -- "Part1 and Part2 can't have the same values"
       AND 
       (part1 <> 5 OR part2 IS NUILL) -- "if Part1 is 5 then Part2 can't have a value"
      );
+1


source


I made the column OnePiece

calculated using this formula:

(CONVERT([bit],CASE [Part1] WHEN (5) THEN (1) ELSE (0) END,0))

For restrictions:

ALTER TABLE [AssemblyData]
ADD CONSTRAINT [CHK_AssemblyData]
CHECK  (([Part2]<>(5))
       AND
       ([Part2]<>[Part1])
       AND
       ([Part1]<>(5) OR [Part2] IS NULL))

Thanks for every user contributing to the answer to this question.

0


source






More articles:


All Articles