Check multiple columns with one value

Question

Post reply

Check multiple columns with one value

djj

SSCoach

Points: 18831
More actions
May 21, 2014 at 7:03 am

#293483

I keep thinking this can be done in one query:UPDATE H
SET Phone1 = NULL
FROM Dialer.dbo.Agentless_Hold H
INNER JOIN Dialer.dbo.PhoneLookup D ON H.Phone1 = D.Phone;
UPDATE H
SET Phone2 = NULL
FROM Dialer.dbo.Agentless_Hold H
INNER JOIN Dialer.dbo.PhoneLookup D ON H.Phone2 = D.Phone;
UPDATE H
SET Phone3 = NULL
FROM Dialer.dbo.Agentless_Hold H
INNER JOIN Dialer.dbo.PhoneLookup D ON H.Phone3 = D.Phone;
UPDATE H
SET Phone4 = NULL
FROM Dialer.dbo.Agentless_Hold H
INNER JOIN Dialer.dbo.PhoneLookup D ON H.Phone4 = D.Phone;
-- total of ten
Lookup table has one row per phone number
CREATE TABLE PhoneLookup (Phone VARCHAR(10));
Hold table has up to ten phone numbers per row
CREATE TABLE Agentless_Hold (RefNum VARCHAR(10), Phone1 VARCHAR(10),
Phone2 VARCHAR(10), Phone3 VARCHAR(10), Phone4 VARCHAR(10),
Phone5 VARCHAR(10), Phone6 VARCHAR(10), Phone7 VARCHAR(10),
Phone8 VARCHAR(10), Phone9 VARCHAR(10), Phone10 VARCHAR(10));
I need to NULL any phone number in the Hold table where the number is in the Lookup table
So I was wondering if there was a way to do this in one statement?
Thanks

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

UPDATE h SET

Phone1 = CASE WHEN d1.Phone IS NOT NULL THEN NULL ELSE Phone1 END,

Phone2 = CASE WHEN d2.Phone IS NOT NULL THEN NULL ELSE Phone2 END,

Phone3 = CASE WHEN d3.Phone IS NOT NULL THEN NULL ELSE Phone3 END,

.

FROM Dialer.dbo.Agentless_Hold h

LEFT JOIN Dialer.dbo.PhoneLookup d1 ON d1.Phone = h.Phone1

LEFT JOIN Dialer.dbo.PhoneLookup d2 ON d2.Phone = h.Phone2

LEFT JOIN Dialer.dbo.PhoneLookup d3 ON d3.Phone = h.Phone3

.

or this:

UPDATE h SET

Phone1 = CASE WHEN d.Phone = h.Phone1 THEN NULL ELSE Phone1 END,

Phone2 = CASE WHEN d.Phone = h.Phone2 THEN NULL ELSE Phone2 END,

Phone3 = CASE WHEN d.Phone = h.Phone3 THEN NULL ELSE Phone3 END,

.

FROM Dialer.dbo.Agentless_Hold h

LEFT JOIN Dialer.dbo.PhoneLookup d

ON d.Phone IN (h.Phone1,h.Phone2,h.Phone3,h.Phone4,h.Phone5,h.Phone6,h.Phone7,h.Phone8,h.Phone9,h.Phone10)

.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

djj SSCoach Points: 18831 More actions · Answer 2

ChrisM@Work (5/21/2014)
UPDATE h SET Phone1 = CASE WHEN d1.Phone IS NOT NULL THEN NULL ELSE Phone1 END, Phone2 = CASE WHEN d2.Phone IS NOT NULL THEN NULL ELSE Phone2 END, Phone3 = CASE WHEN d3.Phone IS NOT NULL THEN NULL ELSE Phone3 END, . . . FROM Dialer.dbo.Agentless_Hold h LEFT JOIN Dialer.dbo.PhoneLookup d1 ON d1.Phone = h.Phone1 LEFT JOIN Dialer.dbo.PhoneLookup d2 ON d2.Phone = h.Phone2 LEFT JOIN Dialer.dbo.PhoneLookup d3 ON d3.Phone = h.Phone3 . .
.
or this:
UPDATE h SET
Phone1 = CASE WHEN d.Phone = h.Phone1 THEN NULL ELSE Phone1 END,
Phone2 = CASE WHEN d.Phone = h.Phone2 THEN NULL ELSE Phone2 END,
Phone3 = CASE WHEN d.Phone = h.Phone3 THEN NULL ELSE Phone3 END,
.
.
.
FROM Dialer.dbo.Agentless_Hold h
LEFT JOIN Dialer.dbo.PhoneLookup d
ON d.Phone IN (h.Phone1,h.Phone2,h.Phone3,h.Phone4,h.Phone5,h.Phone6,h.Phone7,h.Phone8,h.Phone9,h.Phone10)
.
.

Thanks! I had thought about the first one, however the second one looks interesting.

djj SSCoach Points: 18831 More actions · Answer 3

Here is what I ended up withUPDATE H SET

Phone1 = NULLIF(Phone1, D.Phone),

Phone2 = NULLIF(Phone2, D.Phone),

Phone3 = NULLIF(Phone3, D.Phone),

Phone4 = NULLIF(Phone4, D.Phone),

Phone5 = NULLIF(Phone5, D.Phone),

Phone6 = NULLIF(Phone6, D.Phone),

Phone7 = NULLIF(Phone7, D.Phone),

Phone8 = NULLIF(Phone8, D.Phone),

Phone9 = NULLIF(Phone9, D.Phone),

Phone10 = NULLIF(Phone10, D.Phone)

FROM Dialer.dbo.Agentless_Hold h

LEFT JOIN Dialer.dbo.PhoneLookup d

ON d.Phone IN (h.Phone1,h.Phone2,h.Phone3,h.Phone4,h.Phone5,h.Phone6,h.Phone7,h.Phone8,h.Phone9,h.Phone10)

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

NULLIF - good catch.

The new query is much more compact and readable than the original, how does it compare in terms of performance?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

djj SSCoach Points: 18831 More actions · Answer 5

There is actually only a small number of records so performance is hard to establish. And I have not done the execution plans.

However, reading only one query is a lot better and if I need to add a second table to check against only one table name needs changed in the cut/paste.:-)