how to change a lot of records at once in phpmyadmin

I'd like to know how to update several records at once where a certain column type is selected.

I have found how to selec开发者_运维技巧t records. I have found how to update records. But i don't know how to do it both together for it to work.

Selecting:

SELECT * FROM users WHERE 'type'='new'

Updating:

update table

set column = 937

So basically i want to change the info in the 'column' to 937 in the 'table' if another column 'type' is 'new'.

Thanks,

You can do this by simply adding a WHERE clause to your UPDATE statement:

UPDATE `users`
SET `myColumn` = 937
WHERE `type` = 'new'

Of course, change myColumn to match your column name

You can do this with a subquery :

update users
set column = 937
where id in (select id from users where type='new')

Just change the columns name if I got them wrong.

After researching for a while I found another solution to this problem. When referencing the same table or field name in a query, the name space in MySQL ends up with duplicates and fails. To overcome this use the "AS" syntax to name the duplicate items. Also, update queries often require a key field to be used as the parameter for the where clause, this is often your auto-incremented ID field. This example provides a solution for both of these problems.

    UPDATE tbl 
    SET field=newValue 
    WHERE tbl.key IN 
        (SELECT idNew FROM 
            (Select tbl.key AS idNew 
            FROM tbl 
            WHERE field=editValue) AS tblNew);

• tbl - Table name being updated

• field - Target field for update

• newValue - Value to replace items in the target field

• tbl.key - Field name for the key in your table

• idNew - New name for key in your table, to replace the name and prevent failure from duplicating names in query. could be any alphanumeric string. 'id' or 'id_new' or 'id_n'

• editValue - The value to change

• tblNew - New name for query, to prevent duplicate table names in the query. Could be any alphanumeric string 'tbl_n' or 'tbl_new' or 'tbl_test'

This query gets the key values for all the records that match records that have values you want edited, then changes the names of the key and tbl so they can be processed by MySQL, lastly the update query runs and changes the values based on the keys provided in the sub-query.

Hopefully this saves someone else a few hours!