Question

我有一些这样的法典。在表格中,我还必须保留一个自动加油领域(在其他表格中使用)。我要简化和优化这一守则。

$query ="SELECT * FROM models WHERE col1 =  foo ";
$testResult = mysql_query($query) or die( Error, query failed );    

if(mysql_fetch_array($testResult) == NULL){
    //insert...
    $query ="INSERT INTO models (col1, col2, col3)
    VALUES ( foo ,  bar ,  alph )";
    $result = mysql_query($query) or die( Error, query failed );
}else{
    //update...
    $query = "UPDATE models
        SET col1= foo , col2= bar , col3= alph 
        WHERE col1= foo  AND col2= bar ";
        $result = mysql_query($query) or die( Error, query failed );        
}

Edit:主要关键补贴是自动加油的领域。我永远不想改变这一点。然而,当另一个领域出现/重复时,这就是我要更新这一记录。

Answer 1

• 如何对待青少年:

REPLACE INTO models
( col1, col2, col3 )
VALUES
(  foo ,  bar ,  alpha  )

假设第1栏是你的主要关键,如果已经存在价值 f,则它将更新其他两栏。否则,它将插入新的一行。

Answer 2

你只需要更新关于重复的第3栏。这正是你所要求的。

INSERT INTO models (col1, col2, col3)
VALUES ( foo ,  bar ,  alpha )
ON DUPLICATE KEY UPDATE col3= alpha ;

Answer 3

您可尝试使用“INSERT...... ON DUPLICATE KEY UPDATE” syntax

rel=“noreferer”http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html。

If you specify ON DUPLICATE KEY UPDATE, and a row is inserted that would cause a duplicate value in a UNIQUE index or PRIMARY KEY, an UPDATE of the old row is performed. For example, if column a is declared as UNIQUE and contains the value 1, the following two statements have identical effect:

INSERT INTO table (a,b,c) VALUES (1,2,3)
  ON DUPLICATE KEY UPDATE c=c+1;

UPDATE table SET c=c+1 WHERE a=1;
The ON DUPLICATE KEY UPDATE clause can contain multiple column assignments, separated by commas.

With ON DUPLICATE KEY UPDATE, the affected-rows value per row is 1 if the row is inserted as a new row and 2 if an existing row is updated.

If column b is also unique, the INSERT is equivalent to this UPDATE statement instead:

UPDATE table SET c=c+1 WHERE a=1 OR b=2 LIMIT 1;
If a=1 OR b=2 matches several rows, only one row is updated. In general, you should try to avoid using an ON DUPLICATE KEY clause on tables with multiple unique indexes.

You can use the VALUES(col_name) function in the UPDATE clause to refer to column values from the INSERT portion of the INSERT ... UPDATE statement. In other words, VALUES(col_name) in the UPDATE clause refers to the value of col_name that would be inserted, had no duplicate-key conflict occurred. This function is especially useful in multiple-row inserts. The VALUES() function is meaningful only in INSERT ... UPDATE statements and returns NULL otherwise. Example:

INSERT INTO table (a,b,c) VALUES (1,2,3),(4,5,6)
  ON DUPLICATE KEY UPDATE c=VALUES(a)+VALUES(b);
That statement is identical to the following two statements:

INSERT INTO table (a,b,c) VALUES (1,2,3)
  ON DUPLICATE KEY UPDATE c=3;
INSERT INTO table (a,b,c) VALUES (4,5,6)
  ON DUPLICATE KEY UPDATE c=9;
If a table contains an AUTO_INCREMENT column and INSERT ... UPDATE inserts a row, the LAST_INSERT_ID() function returns the AUTO_INCREMENT value. If the statement updates a row instead, LAST_INSERT_ID() is not meaningful. However, you can work around this by using LAST_INSERT_ID(expr). Suppose that id is the AUTO_INCREMENT column. To make LAST_INSERT_ID() meaningful for updates, insert rows as follows:

INSERT INTO table (a,b,c) VALUES (1,2,3)
  ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id), c=3;
The DELAYED option is ignored when you use ON DUPLICATE KEY UPDATE.

Answer 4

MSQL 5.0 + 用途:INSERT ON DUPLICATE KEY UPDATE

rel=“nofollow”http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html

友情链接