CODEWITHSUNDEEP
sql-servert-sql
AGx-07_162
AGx-07_162

Reputation: 301

IF....THEN statement in T-SQL to perform UPDATE

Disclaimer: I am still learning SQL so I apologize if my question comes off as amateur-ish or is otherwise a very simple answer. I have no formal training. I am teaching myself. Thanks.

A particular query was created to update the EMAIL column with data in the EMAIL2 column should the EMAIL column be blank. This query goes on to grab data from the EMAIL3-6 columns should any prior ones also be blank in an attempt to populate the EMAIL column. It currently sits as follows:

update Parents set email = email2 where email = ' ' OR email is null go

update Parents set email = email3 where email = ' ' OR email is null go

update Parents set email = email6 where email = ' ' OR email is null go

(and so on)

Is there a more simple way, using some sort of IF...THEN type conditions to check for blank or null in the EMAIL column and populate it based on data in the secondary column(s)? These columns would also need to be checked for blank or null values and skipped if blank or null is true.

I appreciate any advice that can be given. The above query works it just doesn't feel like the best way to go about doing it.

Upvotes: 3

Views: 681

Answers (3)

Matt
Matt

Reputation: 1431

Not to make this a tutorial on COALESCE and NULLIF but to proof everything @billinkc provided in his answer, this is why that works. (Sorry I was working on the solution as he answered it). Plop this into SSMS and have a look at the results. A simple update like shown above will do nicely though.

Just Discovered the SQL Fiddle Resource: SQL Fiddle

--Setup the temp table
DECLARE @Parents TABLE (
    ParentsId INT IDENTITY (1,1) PRIMARY KEY,
    email varchar(max),
    email2 varchar(max),
    email3 varchar(max),
    email4 varchar(max),
    email5 varchar(max),
    email6 varchar(max)
)

--This would be the pull from your real Parents Table.
INSERT INTO @Parents 
SELECT
    NULL,'[email protected]',' ',NULL,NULL,NULL
UNION ALL
SELECT
    NULL,'   ',NULL,NULL,NULL,'[email protected]'
UNION ALL
SELECT
    NULL,'',NULL,NULL,NULL,'[email protected]'

--Look at the data before we cleanse it
SELECT * FROM @Parents

--Take a look at what COALESCE looks like before the cleanse
SELECT ParentsId, COALESCE(email2,email3,email4,email5,email6) AS NewEmail FROM @Parents

--RUN the NULLIF
UPDATE @Parents SET 
    email2 = NULLIF(email2,' '),
    email3 = NULLIF(email3,' '),
    email4 = NULLIF(email4,' '),
    email5 = NULLIF(email5,' '),
    email6 = NULLIF(email6,' ')

SELECT * FROM @Parents

--Take a look at what COALESCE looks like after the cleanse
SELECT ParentsId, COALESCE(email2,email3,email4,email5,email6) AS NewEmail FROM @Parents

Upvotes: 1

Mithrandir
Mithrandir

Reputation: 25337

Do you mean something like this?

update Parents 
      set email = COALESCE(
                    NULLIF(LTRIM(RTRIM(email2), '')),
                    NULLIF(LTRIM(RTRIM(email3), '')),
                    NULLIF(LTRIM(RTRIM(email4), '')),
                    NULLIF(LTRIM(RTRIM(email5), '')),
                    NULLIF(LTRIM(RTRIM(email6), ''))
                  ) 
where email = ' ' OR email is null

The 

LTRIM(RTRIM(email2)

will make sure the convert ' ' to an empty string '', since SQL Server has no trim, but two separate functions LTRIM and RTRIM. NULLIF returns null if the two expressions are equal. So if any of the email cols is null or just ' ' it will return null.

The COALESCE function will return the value of the first expression, that is not null.

Upvotes: 3

billinkc
billinkc

Reputation: 61211

A handy function you will want to become aquainted with is NULLIF. It allows you to simplify your logic in cases where you might like to treat a value like a NULL. For example, if an application was putting a sentinel value of 'NA' in a NULLable column column1, NULLIF(column1, 'NA') would return the NULL value for all the NULLs and all the NAs. In your case, we'd use this trick to convert empty strings into NULLs.

The other thing we'll do is trim all the empty strings down so our NULLIF only needs to check for the scenario of '' (instead of '', ' ', ' ', ad nauseum). TSQL only provides LTRIM and RTRIM and I have a habit of using RTRIM although trimming an empty string from either direction will result in our desired state. NULLIF(RTRIM(column1),'')

Using that expression, we will now have the right thing to plug into the COALESCE function. Thus

UPDATE
    P
SET
    email = COALESCE(NULLIF(RTRIM(P.email2), ''), NULLIF(RTRIM(P.email3), ''), NULLIF(RTRIM(P.email4), ''))
FROM
    dbo.Parents P
WHERE
    -- This will force a table scan which can be a bad thing for performance
    -- Generally, one should avoid wrapping functions around a column and instead
    -- apply them to the thing being tested
    NULLIF(RTRIM(P.email), '') IS NULL

Upvotes: 3

Related Questions