Post reply

Working with a Very Poorly Designed Database

  • October 25, 2013 at 3:47 pm

    #285240

    Hello everyone, and Happy Friday

    I am tasked with trying to update a table. I have told them that the table is not at all designed correctly, but no one seems to listen. A Java front-end developer designed this database, if you can believe that. Now they want me to perform some miracle when it comes to getting the data out.

    The query at the bottom pretty much tells the logic story. I need to update a Category Column depending on the value in the score column.

    CREATE TABLE MemberScore

    (

    Score float

    , Category1 bit

    , Category2 bit

    , Category3 bit

    , Category4 bit

    , Category5 bit

    )

    INSERT INTO MemberScore

    SELECT 0.1,0,0,0,0,0 UNION ALL

    SELECT 0.2,0,0,0,0,0 UNION ALL

    SELECT 0.4,0,0,0,0,0 UNION ALL

    SELECT 0.55,0,0,0,0,0 UNION ALL

    SELECT 0.65,0,0,0,0,0 UNION ALL

    SELECT 0.8,0,0,0,0,0 UNION ALL

    SELECT 0.97,0,0,0,0,0 UNION ALL

    SELECT 0.99,0,0,0,0,0 UNION ALL

    SELECT 0.12,0,0,0,0,0 UNION ALL

    SELECT 0.23,0,0,0,0,0 UNION ALL

    SELECT 0.34,0,0,0,0,0 UNION ALL

    SELECT 0.56,0,0,0,0,0 UNION ALL

    SELECT 0.54,0,0,0,0,0 UNION ALL

    SELECT 0.98,0,0,0,0,0 UNION ALL

    SELECT 0.95,0,0,0,0,0 UNION ALL

    SELECT 0.91,0,0,0,0,0 UNION ALL

    SELECT 0.29,0,0,0,0,0 UNION ALL

    SELECT 0.36,0,0,0,0,0 UNION ALL

    SELECT 0.88,0,0,0,0,0 UNION ALL

    SELECT 0.68,0,0,0,0,0 UNION ALL

    SELECT 0.88,0,0,0,0,0 UNION ALL

    SELECT 0.77,0,0,0,0,0 UNION ALL

    SELECT 0.75,0,0,0,0,0 UNION ALL

    SELECT 0.48,0,0,0,0,0 UNION ALL

    SELECT 0.5,0,0,0,0,0 UNION ALL

    SELECT 0.69,0,0,0,0,0 UNION ALL

    SELECT 0.66,0,0,0,0,0 UNION ALL

    SELECT 0.37,0,0,0,0,0 UNION ALL

    SELECT 0.42,0,0,0,0,0 UNION ALL

    SELECT 0.4,0,0,0,0,0 UNION ALL

    SELECT 0.27,0,0,0,0,0 UNION ALL

    SELECT 0.33,0,0,0,0,0 UNION ALL

    SELECT 0.57,0,0,0,0,0

    SELECT * FROM MemberScore

    -- Logic for the query

    UPDATE MemberScore

    SELECT Score FROM MemberScore(

    CASE WHEN ( Score > 0.0 AND Score < 0.4 ) THEN (UPDATE MemberScore SET Category1 = 1)

    CASE WHEN ( Score > 0.4 AND Score < 0.55 ) THEN (UPDATE MemberScore SET Category2 = 1)

    CASE WHEN ( Score > 0.55 AND Score < 0.66 ) THEN (UPDATE MemberScore SET Category3 = 1)

    CASE WHEN ( Score > 0.66 AND Score < 0.85 ) THEN (UPDATE MemberScore SET Category4 = 1)

    CASE WHEN ( Score > 0.85) THEN (UPDATE MemberScore SET Category5 = 1)

    END)

    Please try not to laugh too hard at this awful mess of a table design and query. I would greatly appreciate any and all assistance.

    Thank you in advance for all your assistance, comments and suggestions.

    Andrew SQLDBA

  • October 25, 2013 at 4:10 pm

    #1661758

    It's not that difficult, you can do it this way or with 5 UPDATEs, one for each condition.

    UPDATE m SET

    Category1 = CASE WHEN ( Score > 0.0 AND Score < 0.4 ) THEN 1 ELSE Category1 END,

    Category2 = CASE WHEN ( Score > 0.4 AND Score < 0.55 ) THEN 1 ELSE Category2 END,

    Category3 = CASE WHEN ( Score > 0.55 AND Score < 0.66 ) THEN 1 ELSE Category3 END,

    Category4 = CASE WHEN ( Score > 0.66 AND Score < 0.85 ) THEN 1 ELSE Category4 END,

    Category5 = CASE WHEN ( Score > 0.85) THEN 1 ELSE Category5 END

    FROM #MemberScore m

    You're missing equal signs (=) somewhere in your conditions but I'm uncertain of which ones, maybe the on the bottom limits.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • October 25, 2013 at 5:21 pm

    #1661764

    I had a disaster like this once, but it was in [ick] Access. After I restructured and showed them how simple it was to query, they were sold.

  • October 25, 2013 at 5:31 pm

    #1661765

    Thanks Luis, that is exactly. I was close, but just could not get it correct.

    Have a nice weekend

    Andrew SQLDBA

  • October 25, 2013 at 5:32 pm

    #1661766

    Just a thought - category1 thru category5 could just be computed fields. You already have all the information you need in Score.

  • October 25, 2013 at 6:07 pm

    #1661768

    lnardozi 61862 (10/25/2013)

    Just a thought - category1 thru category5 could just be computed fields. You already have all the information you need in Score.

    I'm sure that Andrew can't change the table structure. If that would be possible, five category columns are not ideal. The best option would be to have a Categories table and a category column in the MemberScore table to reference the Categories table (a.k.a. a Foreign Key).

    I'm glad I could help, have a good weekend you too.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

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

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