Tech Off Thread

7 posts

Forum Read Only

This forum has been made read only by the site admins. No new threads or comments can be added.

Changing from varchar key to int key

Back to Forum: Tech Off
  • User profile image
    qwert231

    Duh, shoulda seen this coming.

    In an effort to be 'quick' we have some datatables that have a varchar relation. Here's what we've got:
    Table: Photos
    PhotoID (int)
    ImageOrderNum (int) (related to Events.ImageOrderNum)
    mGroup (varchar(50)) (related to Groupings.mGroup)
    GroupID (int) (New field)
    (Other fields...)


    Table: Events
    ImageOrderNum (int) (related to Photos.ImageOrderNum)
    PhotographerID (int)
    (Other fields...)

    Table: Groupings
    GroupID (int) (New Ident field)
    PhotographerID (int)
    GroupName (varchar(50)) (related to Photos.mGroup)
    (Other fields...)


    I want to get Groupings.GroupID into the Photos table for each respective group. Here's the issue. More than one Photographer may have the same GroupName. Also, some Photos do not have a group associated. Those should get GroupingID 1897.

    Any ideas how to do this? It should be a one time thing.

  • User profile image
    Maurits

    1. Add the Groupings.GroupID field as an IDENTITY but not (yet) a primary key
    2. Add the Photos.GroupID field - default it to NULL

    Run this query...

    UPDATE
        Photos
    SET
        GroupID =
        (
           SELECT
              G.GroupID
           FROM
              Groupings AS G
           WHERE
              G.GroupName = Photos.mGroup
        )
    WHERE
        GroupID IS NULL AND
        GroupID IN
        (
           SELECT
              G.GroupID
           FROM
              Groupings AS G
           WHERE
              G.GroupName = Photos.mGroup
        )

    3. Set Photos.GroupID to no longer allow nulls
    4. Remove the mGroup -> GroupName relationship
    5. Make Groups.GroupID the primary key
    6. Establish the Photos.GroupID -> Groupings.GroupID relationship
    7. Delete the obsolete fields

  • User profile image
    qwert231

    Ok, but you missed one thing. I might have something like this:
    GroupID, PhotographerID, GroupName
    21,12345,Reception
    22,12345,Rehersal
    23,33312,Reception

    But I might be able to work with your query to get that. I created a test table that I can use.

  • User profile image
    qwert231

    This seemed to work on my test Table:

    UPDATE
        PhotosTest
    SET
        GroupID = g.GroupID
    FROM
        PhotosTest p, PhotographerEvents e, Groupings g
    WHERE
        p.ImageOrderNum = e.ImageOrderNum
     AND e.PhotographerID = g.PhotographerID
     AND p.mGroup = g.GroupName

  • User profile image
    Maurits

    You could change SELECT G.GroupID to SELECT MAX(G.GroupID) in the original query to have an ad-hoc disambiguation.  You'll probably want to manually inspect all Photos rows that use duplicated GroupNames though...

    -- photos in ambiguously named groups
    SELECT
        *
    FROM
        Photos
    WHERE
        GroupID IN (
    SELECT
        G2.GroupID
    FROM
        Groups AS G2
    WHERE
        G2.GroupName IN (
    SELECT
        G1.GroupName
    FROM
        Groups AS G1
    GROUP BY
        G1.GroupName
    HAVING
        COUNT(*) > 1
    )
    )

    As an aside to anyone interested in standards that may be reading this thread... be aware that UPDATE / FROM is a Microsoft-ism, not standard SQL.

  • User profile image
    Maurits

    Maurits wrote:
    You'll probably want to manually inspect all Photos rows that use duplicated GroupNames though


    Oh, I see, you're using the ImageOrderNumber field as an additional disambiguation.

    Does that work?  Are all the ImageOrderNumber values different?  Or do they restart at 1 for every event?

  • User profile image
    qwert231

    Each event has it's own ImageOrderNum.
    ImageOrderNum (123456)
     PhotoID (11122)
     PhotoID (11123)
     PhotoID (11124)

    ImageOrderNum (123457)
     PhotoID (11125)
     PhotoID (11126)

Conversation locked

This conversation has been locked by the site admins. No new comments can be made.