conditional statements

[chazoid]

Hi all,

I have a table with 9 fields that store email addresses: email1,email2,email3, etc. I need to add one new email address to several records. Since each record may have a different number of fields already filled, I need to put the new address in the next available field.

This is the query that returns the record set I'm working with.

SELECT *
FROM delivery_options
WHERE (setup_id IN
          (SELECT setup_id
              FROM setup_main
              WHERE client_id = 231))

I think I need to use a CASE statement, then check each email field and only update with the new address if it's null. I'm just having trouble figuring out where to put it in the query.
Maybe there's a better way?
Thanks,
Justin

 

[kjv1611]

I'm not sure you can do this with a CASE statement, though I could be proved wrong. I'd think you'll need 3 different queries (easiest), or possibly a loop that loops through the queries, where it's looking at a different field number each time... and changing the critieria.

To do it as 9 queries, simply write the query for the first field, copy and past it 8 times, then use find/replace to change the fields you're looking at.... and then of course also modify your critieria, since you'll be saying "WHERE email1 IS NULL" in the first one... and then "WHERE email1 IS NULL AND email2 IS NULL AND email3 IS NULL AND email4 IS NULL AND email5 IS NULL AND email6 IS NULL AND email7 IS NULL AND email8 IS NULL" for the last one.

'Course, there may be some fancy function that would handle that more easily.

But actually, is it possible for a structural change here? If you've possibly got up to 9 email addresses per person, I think you should have a separate email table linked on the Person/User ID to the Person/User table. That way, you're not limited... what if the number grows for some reason for instance?

So you could have it setup something like:

USERID [bigint]
EmailAddress [varchar]
IsActive [bit]

Of course, you could even go further than that, I guess, but that would probably suffice. Then, you'd simply be adding records to the email table, pulling the matching UserID from the Users table.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[chazoid]

Thanks! I think the 9 query method should work fine. There's no need to make it too elegant. I'll probably never have to do this again

I agree, the structure is flawed. Your suggestion makes much more sense. It's just that it's a system that's been in place for over 10 years, and they've been talking about retiring it for 6 years.. but still won't allow any changes to it. I gave up on asking to fix certain things.
There are actually two records that do have all 9 address fields filled, but one person has left the company, so we can delete those.

Thanks for the help!