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.

Convert this SQL Select into Delete?

Back to Forum: Tech Off
  • User profile image
    qwert231

    I have this query:
    SELECT     Photos.PhotoID
    FROM         Photos LEFT OUTER JOIN
                          Groupings ON Photos.mGroup = Groupings.GroupName
    WHERE     (Groupings.GroupName IS NULL AND Photos.ImageOrderNum = 290931)

    It returns 49 rows. I'd like to Delete them. But I'm not good at doing Deletes w/ Joins. mGroup and GroupName are varchar fields.

  • User profile image
    GeSpot

    qwert231 wrote:
    I have this query:
    SELECT     Photos.PhotoID
    FROM         Photos LEFT OUTER JOIN
                          Groupings ON Photos.mGroup = Groupings.GroupName
    WHERE     (Groupings.GroupName IS NULL AND Photos.ImageOrderNum = 290931)

    It returns 49 rows. I'd like to Delete them. But I'm not good at doing Deletes w/ Joins. mGroup and GroupName are varchar fields.


    DELETE Photos WHERE PhotoID IN
    (SELECT     Photos.PhotoID
    FROM         Photos LEFT OUTER JOIN
                          Groupings ON Photos.mGroup = Groupings.GroupName
    WHERE     (Groupings.GroupName IS NULL AND Photos.ImageOrderNum = 290931))

  • User profile image
    cooler

    If you only want to delete from the Photos table you can easily do this:

    Delete From Photos where PhotoID in (

       SELECT     Photos.PhotoID
       FROM         Photos LEFT OUTER JOIN
                        Groupings ON Photos.mGroup =   Groupings.GroupName
       WHERE     (Groupings.GroupName IS NULL AND   
          Photos.ImageOrderNum = 290931)

    )

  • User profile image
    Maurits

    Run this SELECT:
    SELECT
        *
    FROM
        Photos
    WHERE
        Photos.ImageOrderNum = 290931 AND
        Photos.mGroup NOT IN -- not sure about mGroup IS NULL
        (
            SELECT
                Groupings.GroupName
            FROM
                Groupings
        )

    And this one:
    SELECT
        *
    FROM
        Photos
    WHERE
        Photos.ImageOrderNum = 290931 AND
        Photos.mGroup IS NULL


    Whichever one returns the rows you want, change the SELECT * to DELETE and you're good

  • User profile image
    gregoryw

    You can also explicitly state which table to delete from at the same time as specifying a join:

    DELETE FROM Photos
        FROM Photos LEFT JOIN Groupings ON Photos.mGroup = Groupings.GroupName
        WHERE  Groupings.GroupName IS NULL
        AND       Photos.ImageOrderNum = 290931

  • User profile image
    qwert231

    GeSpot, you had it. Thanks. (Photos.mGroup would not be Null for those records. These 2 tables, and don't shoot me, use Photos.mGroup, and Groupings.GroupName for a relation.

    Thank you guys.

  • User profile image
    Maurits

    gregoryw wrote:
    You can also explicitly state which table to delete from at the same time as specifying a join:

    DELETE FROM Photos
        FROM Photos LEFT JOIN Groupings ON Photos.mGroup = Groupings.GroupName
        WHERE  Groupings.GroupName IS NULL
        AND       Photos.ImageOrderNum = 290931


    (shudder)
    You can UPDATE FROM a join too, but it scares me.

    EDIT: And it's T-SQL proprietary, so it only works for Microsoft SQL Server.

Conversation locked

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