I have a table of 3 columns A,B,C

initially column C is completely empty and for every entry either A has a number or B has a number (never both in the same row)

I want to create a view that checks for every row if A=x and B is null or 0 then write the value of A in col.

EXAMPLE:

Can someone help guide me, I am still new to sql

CodePudding user response：

You can'z use a VIEW for that but an UPDATE

UPDATE mytable 
    SET C = CASE WHEN A > 0 AND (B IS NULL OR B = 0) THEN A 
            ELSE B END

Thsi will not include what happens wehen A > 0 and B > 0 as you haven't specify what to do so this query will always take A before B

CodePudding user response：

Let's assume a table ABC with column A, B and C as you mentioned

create table abc (a     int ,b      int ,c      int )

And you want to display column C as value of either A or B based upon value then you can create View by two methods to achieve the desired result

1. Using CASE

create view abc_v1
as 
select a, b, case when isnull(a,0)=0 then b else a end "c"
from
abc

2. using Coalesce: considering the values would be either 0 or some value, we can mark column A/B as NULL when value is zero and use Coalesce

create view abc_v2
as
select a,b, coalesce(nullif(a,0),nullif(b,0)) as "c"
from abc

Or else, If you want to update Column C with Value of col A/B then

Update ABC set c = coalesce(nullif(a,0),nullif(b,0))

CodePudding user response：

Try this view. It spells out your requirement.

CREATE OR REPLACE VIEW abc_with_c AS
SELECT a, b,
       CASE WHEN a = 0 OR a IS NULL THEN b
            WHEN b = 0 OR b IS NULL THEN a
            ELSE NULL 
        END AS c
  FROM abc;

It's a good idea in SQL to write statements so they're easy to read and reason about.