Results 1 to 4 of 4

Thread: SQL Help Please

  1. #1
    Join Date
    Mar 2012
    Posts
    27

    Unanswered: SQL Help Please

    I'm quite new to SQL but here goes:

    The intended approach was to create a view selecting unwanted players using the IsBanned value from tblPlayers table and then any records with the values "% canc%" or "%prob%" in the tagcode field of the tblPlTags table.

    Where the PlayerID value matches in both the tblPlayers table and the created view update the IsCall, IsSMSSend, etc fields.

    View Creation - identify Records - called the viewTemp

    SELECT dbo.tblPlayers.PlayerID, dbo.tblPlayers.LastName, dbo.tblPlayers.FirstName, dbo.tblPlayers.IsNoMail, dbo.tblPlayers.IsBanned, dbo.tblPlayers.IsCall,
    dbo.tblPlayers.IsEmailSend, dbo.tblPlayers.IsSMSSend, dbo.tblPlTags.TagCode
    FROM dbo.tblPlayers INNER JOIN
    dbo.tblPlTags ON dbo.tblPlayers.PlayerID = dbo.tblPlTags.PlayerID
    WHERE (dbo.tblPlayers.IsBanned = 1) AND (dbo.tblPlTags.TagCode LIKE '%cancelled%') OR
    (dbo.tblPlTags.TagCode LIKE '%problem%')

    Update Records

    use cmktdb
    update tblPlayers
    set IsNoMail = '1', IsCall = '0', IsEmailSend = '0', IsSMSSend = '0'
    where ViewTemp.PlayerID = tblPlayers.PlayerID

    For some reason the code doesnt seem to selct all the records that Im after.
    As I just want to update the selected record.

    Thanks for taking a look.

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    The intended approach was to create a view selecting unwanted players using the IsBanned value from tblPlayers table
    and then any records with the values "% canc%" or "%prob%" in the tagcode field of the tblPlTags table.
    I thought that you should enclose ORed predicates, like...
    Code:
    SELECT P.PlayerID
         , P.LastName
         , P.FirstName
         , P.IsNoMail
         , P.IsBanned
         , P.IsCall
         , P.IsEmailSend
         , P.IsSMSSend
         , T.TagCode
     FROM  dbo.tblPlayers AS P
     INNER JOIN
           dbo.tblPlTags  AS T
      ON   P.PlayerID = T.PlayerID
     WHERE P.IsBanned = 1
       AND
      (    T.TagCode LIKE '%cancelled%'
       OR  T.TagCode LIKE '%problem%'
      )

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Or update the table directly.

    Code:
    UPDATE tblPlayers P
       SET IsNoMail    = '1'
         , IsCall      = '0'
         , IsEmailSend = '0'
         , IsSMSSend   = '0' 
     WHERE P.IsBanned  = 1
       AND EXISTS
          (SELECT 0
            FROM  dbo.tblPlTags  AS T
            WHERE T.PlayerID = P.PlayerID
              AND
             (    T.TagCode LIKE '%cancelled%'
              OR  T.TagCode LIKE '%problem%'
             )
          )

  4. #4
    Join Date
    Mar 2012
    Posts
    27

    Thanks

    Hey,

    Thanks for the replys, I will test them today.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •