mysql update record question

Posted on 2011-04-27
Last Modified: 2012-05-11
I need to update about 150 records in a mysql table which are all telephone number.  Rather than type the following line 150 times for each identifier  does anyone know of a better way of doing this?  

UPDATE inventory SET supplierId = 4 WHERE identifier = '2225551001';
UPDATE inventory SET supplierId = 4 WHERE identifier = '2225551002;
UPDATE inventory SET supplierId = 4 WHERE identifier = '2225551003';
UPDATE inventory SET supplierId = 4 WHERE identifier = '2225551004';
Question by:ipconfig610
    LVL 13

    Accepted Solution

    UPDATE inventory SET supplierId = 4 WHERE identifier IN ('2225551001','2225551002'....)
    LVL 13

    Expert Comment

    UPDATE inventory SET supplierId = 4
    WHERE identifier between 2225551001 and 2225551004
    LVL 41

    Expert Comment

    Another possibility is to have those 150 telephone numbers in a separate table and then do a join update something like

    update inventory a, tablewithphones b
    set a.supplierid = 4
    where a.identifier = b.phonenumber

    Featured Post

    Highfive Gives IT Their Time Back

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    Suggested Solutions

    APEX (Application Express) is used to develop a web application from Oracle. SQL Workshop is one of the tools that comes with Oracle APEX to query or modify the database objects or to make any changes to the structure.
    Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
    Video by: Steve
    Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now