CODEWITHSUNDEEP
phpmysql
bollo
bollo

Reputation: 1634

mysql update help needed to add prefix to entries

Sorry for the poor title, but I couldn't think of another way to describe it. What I need to do is, add a prefix to certain entries in a column.

For example, a column could have number entries such as: 92209.1,92201,1,92202,1 etc. I need to add a prefix to only numbers without the prefix. 92209.1 becomes TMP92201,1.

I know how to use the UPDATE function, but not sure how to use with this type of query. I would be grateful for any help that you can offer. Thanks

Upvotes: 2

Views: 2229

Answers (2)

VolkerK
VolkerK

Reputation: 96159

Let's assume the table

CREATE TEMPORARY TABLE soFoo (
    id int auto_increment,
    v varchar(32),
    primary key(id)
)

You can select all rows having a v without the prefix via

SELECT
  *
FROM
  soFoo
WHERE
  v NOT LIKE 'TMP%'

and you can limit an update query with the same WHERE clause to only affect rows having a v without the prefix.

UPDATE
  soFoo
SET
  ...
WHERE
  v NOT LIKE 'TMP%'

Now you want to set those vs to their current value prefixed with your static string literal

SET
  v = CONCAT('TMP', v)

complete query:

UPDATE
  soFoo
SET
  v = CONCAT('TMP', v)
WHERE
  v NOT LIKE 'TMP%'

self-contained example:

<?php
$pdo = new PDO('mysql:host=localhost;dbname=test;charset=utf8', 'localonly', 'localonly');
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
setup($pdo);

echo "before:\n";
foreach( $pdo->query("SELECT * FROM soFoo", PDO::FETCH_ASSOC) as $row ) {
    echo join(' | ', $row), "\n";
}

$query = "
    UPDATE
        soFoo
    SET
        v = CONCAT('TMP', v)
    WHERE
        v NOT LIKE 'TMP%'
";
$pdo->exec($query);

echo "\n\nafter:\n";
foreach( $pdo->query("SELECT * FROM soFoo", PDO::FETCH_ASSOC) as $row ) {
    echo join(' | ', $row), "\n";
}

function setup($pdo) {
    $pdo->exec('CREATE TEMPORARY TABLE soFoo (
        id int auto_increment,
        v varchar(32),
        primary key(id)
    )');

    $stmt = $pdo->prepare('INSERT INTO soFoo (v) VALUES (:v)');
    $stmt->bindParam(':v', $v);
    // add some rows with and some without the prefix
    for($i=0; $i<20; $i++) {
        $v = rand(1000, 10000);
        if ( 0==$v%2 ) {
            $v = 'TMP'.$v;
        }
        $stmt->execute();
    }
}

Upvotes: 4

Romain
Romain

Reputation: 12819

You should use a statement along the lines of:

UPDATE TABLE_NAME SET FIELD_NAME = CONCAT('TMP', RIGHT(FIELD_NAME, LENGTH(FIELD_NAME) - 7)) WHERE FIELD_NAME LIKE '92209.1%'

Essentially, it'll change the value of the field named FIELD_NAME from the table called TABLE_NAME for every row that has a FIELD_NAME starting with '92209.1'. The value that will be set is obtained by concatenating 'TMP' and the value of FIELD_NAME to which we removed the left portion of the size of the prefix (7 characters).

Upvotes: 5

Related Questions