Search code examples
sqlsql-update

How do I write a query that resets the data to all the characters who have NEVER won? Using update

How do I write a query that resets data for all characters that have NEVER won? Using the update.

That is, I have a character A who Won and Lost (and this information is on two different lines). And character B, who just lost. The bottom line is that the request should UPDATE the level of the character (make it equal to zero), which has never won, i.e. character B, and character A should not be touched.

In the table, these are characters under the codes: Character A = person_code (3) Character B = Person_code (10) enter image description here

I can't figure out what needs to be written in the second part of the code to get the correct request

UPDATE Table1
SET lvl = 0
WHERE The_team_has_won NOT IN (
    SELECT
        The_team_has_won
    FROM Table1
    WHERE The_team_has_won = 1
)
Solution

  • OPTION 1

    UPDATE
  Table1
SET
  lvl = 0
WHERE
  Person_code NOT IN (
    SELECT
      Person_code
    FROM
      Table1
    WHERE
      The_team_has_won = TRUE
  )

    OPTION 2

    UPDATE
  t1
SET
  lvl = 0
FROM
  Table1 AS t1  
WHERE
  NOT EXISTS (
    SELECT
      *
    FROM
      Table1 AS t2
    WHERE
      (t2.The_team_has_won = TRUE) AND (t2.Person_code = t1.Person_code)
  )