#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2011
    Posts
    2
    Rep Power
    0

    SQL Update Query problem


    I have a table called `TABLE_2` which has a list of unique identifiers, field: `IDENTIFIER`

    I have a second table called `TABLE_1` which contains many columns but we are only interested in `IDENTIFIER` and `FIELD_TO_UPDATE`.

    Currently the `FIELD_TO_UPDATE` field in the `TABLE_1` table has a bunch of records with incorrect values (‘BAD DATA’). Any `IDENTIFIER` listed in `TABLE_2` and `TABLE_1` should have its `FIELD_TO_UPDATE` field changed in the `TABLE_1` table to a different value (‘GOOD DATA’).


    UPDATE `DATABASE`.`TABLE_1`
    SET `DATABASE`.`TABLE_1`.`FIELD_TO_UPDATE` = 'GOOD DATA'
    WHERE `DATABASE`.`TABLE_1`.`IDENTIFIER` =
    (
    SELECT temp.IDENTIFIER
    FROM
    (
    SELECT b.*
    FROM `DATABASE`.`TABLE_2` AS a, `DATABASE`.`TABLE_1` AS b
    WHERE b.`IDENTIFIER` = a.`IDENTIFIER`
    ) AS temp
    WHERE temp.`FIELD_TO_UPDATE` = 'BAD DATA'
    )

    The inner queries work and return a list of IDENTIFIERS but the whole things outputs a "0 Rows Affected". Anyone notice an issue? Thanks.
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,150
    Rep Power
    4274
    Originally Posted by Panther256
    Any `IDENTIFIER` listed in `TABLE_2` and `TABLE_1` should have its `FIELD_TO_UPDATE` field changed in the `TABLE_1` table to a different value (‘GOOD DATA’).
    i'm going to give you a query for this requirement, rather than try to understand what you're doing in your query
    Code:
    UPDATE database.table_1 
    INNER
      JOIN database.table_2
        ON database.table_2.identifier = 
           database.table_1.identifier
       SET database.table_1.field_to_update = 'good data'
     WHERE database.table_1.field_to_update = 'bad data'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2011
    Posts
    2
    Rep Power
    0

    Thank you


    That worked like a charm.

    I don't do much SQL anymore so I can get lost as you obviously saw. I already figured out what was wrong with what I was trying to do: Pass back a list of the IDENTIFIERS to the UPDATE clause... But of course you can only update 1 item at a time this way.

    I appreciate your help and speedy response! Thank you!

IMN logo majestic logo threadwatch logo seochat tools logo