Database help

Started by Biology Forums, September 01, 2014, 11:33:56 PM

Previous topic - Next topic

Biology Forums

I would like to learn how to use the REPLACE function in MYSQL.

The table contains hundreds of rows and is made up 5 columns - A B C D E

I would like to replace all data found in the rows of column C. How do I do that while at the same time keep all rows that corresspond to column A B D and E untouched?

foreach($mergeData as $key => $val) {
$time = strtotime($key);
$myDate = date('Y-m-d', $time);

        $request = db_query("
            REPLACE INTO {$db_prefix}SOMETABLE (A, B, C, D) values('', $val[topic_count], '', '')",
        __FILE__, __LINE__);
    }

Arantor

You use UPDATE rather than REPLACE for that.

REPLACE is for when you actually need to remove the row before an insertion, and you require all the columns to be provided (or use table defaults).

In your case, you'd have to have all the values for A, B, D and E, and re-insert every row, which is all REPLACE is - it's a DELETE then an INSERT.

Biology Forums

You mean like this?

$request = db_query("
            UPDATE {$db_prefix}SOMETABLE (C) values($val[topic_count])",
        __FILE__, __LINE__);

Arantor

No, and since I have no idea how $val['topic_count'] relates to anything else in the table, I can't tell you what the correct answer is.

An UPDATE query comes in two basic forms.

UPDATE table SET column = newvalue - applies to all rows
UPDATE table SET column = newvalue WHERE somecriteria - applies to all rows that match somecriteria

It is perfectly possible to update with some dynamic component, e.g. UPDATE smf_topics SET views = views + 1 WHERE ID_TOPIC = 1

In your case you seem to have a bunch of values that need to be tied to something already in the table (like its primary key) which means an UPDATE per row. (And no, REPLACE is no use to you either because then you'd still have to get all the rest of the data to reinsert it anyway)

Advertisement: