Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2005
    Posts
    2

    Unanswered: How can I optimize this?

    I have a table called `inidviduals` that have 10,000 records in the table and I want the query to change only the specific ones.

    Table Structure is:
    CREATE TABLE individuals (
    `ID` int(10) unsigned NOT NULL default '0',
    `Last_Name` varchar(255),
    `Soundex_Code` varchar(10),
    PRIMARY KEY (`ID`)
    );

    Current query is:
    UPDATE individuals
    SET Soundex_Code = CASE WHEN Last_Name = 'Aaron' THEN 'A650'
    WHEN Last_Name = 'Abercrombie' THEN 'A162'
    WHEN Last_Name = 'Aberlenc' THEN 'A164'
    ELSE '' END;

    Above query modifies ALL records, because of ELSE ''.

    How can I optimize this to update only those records which matches in one query?
    Last edited by gurpreets; 09-26-05 at 08:17.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    UPDATE individuals
    SET Soundex_Code = CASE WHEN Last_Name = 'Aaron' THEN 'A650'
    WHEN Last_Name = 'Abercrombie' THEN 'A162'
    WHEN Last_Name = 'Aberlenc' THEN 'A164'
    END
    where Last_Name in ('Aaron','Abercrombie','Aberlenc')
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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