Mysql how to replace multiple values in 1 Column

Question

My table has a column titled Views with last weeks values.

I want to replace these old values with new values, all of them.

I have tried

UPDATE `Table` SET `views`=35490904, 4013, 1867953, 21558, 12237;

&

REPLACE INTO `Table` (views) VALUES (35490904, 4013, 1867953, 21558, 12237);

where am I going wrong?

Wait, what are you replacing/updating? SET views TO on WHAT? — majidarif, Mar 13 '14 at 18:49
I want to replace all the old values in the column `Views` with the new values. — user3386034, Mar 13 '14 at 18:51
It looks like you are trying to update all the rows with one statement, you need to iterate through all the rows and change one value at a time — Chitowns24, Mar 13 '14 at 18:55
[Check this][1].similar to what you asked [1]: http://stackoverflow.com/questions/10177208/update-a-column-value-replacing-part-of-a-string — codelover, Mar 13 '14 at 19:06
@codelover: click *help* link below *Add Comment* button to get help on how to format a comment being posted. — Ravinder Reddy, Mar 13 '14 at 19:08
@Ravinder,trying to solve it.But the answer I post is adding in the comment area. — codelover, Mar 13 '14 at 19:12

score 0 · Answer 1 · answered Mar 13 '14 at 18:58

Have a look at the sql-syntax for UPDATE:

UPDATE views SET property1=35490904, property2=4013, property3=1867953, property4=21558, property5=12237 WHERE ...;

You have to replace the property parts with the real names of your table columns. I assumed that these are all integers, so if these should be inserted as strings add the ' around the values.

In the WHERE part you can specify wich rows should be updated. If you leave it empty all rows will be updated with the given values.

Yes I want to update all rows, all rows in 1 column with the 5 different values. — user3386034, Mar 13 '14 at 19:02

score 0 · Answer 2 · answered Mar 13 '14 at 19:14

0

You cannot UPDATE multiple rows with different values in one statement. You will need multiple queries.

<?php
$values = array
(
    /* row_id => column_value */
);

// Example code using PDO, feel free to use msqli instead
$db = /* Get database object instance */
$query = $db->prepare('UPDATE Table SET views=:column_value WHERE id=:row_id');
foreach($values as $row_id => $column_value)
{
    $result = $query->execute(array(
        ':row_id' => $row_id,
        ':column_value' => $column_value
    ));
    /* Confirm that $return is TRUE/check for errors if desired */
}

answered Mar 13 '14 at 19:14

Brian S

4,612
4
24
42

I have a text file with all the values in order like this 35490904, 4013, 1867953, 21558, 12237, there is no way for me to simply put this in my database? If I have over 100 values that is the only way? – user3386034 Mar 13 '14 at 19:24
Is there a particular column identifying which row those values go into, or is your text file the total data set for the table? If the latter, just `DELETE FROM Table` to get rid of all the old values, then `INSERT INTO Table (views) VALUES (123), (234), (345), ...etc` – Brian S Mar 13 '14 at 19:47
With prepared statements on the multi-insert, I recommend building a query string with unnamed parameters (`VALUES (?), (?), (?)...`) and then executing with a non-associative array. – Brian S Mar 13 '14 at 19:49

Mysql how to replace multiple values in 1 Column

2 Answers2