php - Move data to new table AND add new data -

i'm trying achieve following 1 sql query (for mysql):

i want move row 1 table table , meanwhile change 2 of values.

can achieve in one query? php code have now:

$value1a = "old value column"; $value1b = "new value column"; $value2 = "value2";  $sql = "insert table2 (date, value1b, value2, value3, value4)             select (date, value1a, value2, value3, value4)             table1             value1a=? , value2=?         delete table1             value1a=? , value2=?"; $stmt = $db->prepare($sql); $stmt->execute(array($value1a, $value2, $value1a, $value2)); 

value2, value3 , value4 stay same. 'date' should change current date (i used sql function 'now()' that). 'value1b' new value, different column name same datatype.

could possible answer?

$sql = "insert table2 (date, value1b, value2, value3)             select (value3, value4)             table1             value1a=? , value2=?         values (now(), ?, value2, value3, value4)         delete table1             value1a=? , value2=?"; $stmt = $db->prepare($sql); $stmt->execute(array($value1a, $value2, $value1b, $value1a, $value2)); 

when performing insert ...select from statement, can use static values directly in select list, function calls (like now()). may construct select statement include needed values in 1 go:

insert table2 (date, value1b, value2, value3, value4)   select     /* call now() in select */     now(),     /* use static string value1b column */     /* (the alias 'as value1b' isn't needed) */     'new value column' value1b,     /* these don't change, , actual column values table1 */     value2,     value3,     value4   table1   value1a = ? , value2 = ? 

when executing statement now, have 2 parameters. use parameter rather static string value1b. want if value derived user input.

insert table2 (date, value1b, value2, value3, value4)   select     /* call now() in select */     now(),     /* parameter here instead of static string.... */     ?,     value2,     value3,     value4   table1   value1a = ? , value2 = ? 

...then call execute() array of 3 parameters.

$stmt->execute(array($value1b, $value1a, $value2)); 

the deletion statement must executed separate statement, after successful execution of insert. assuming want maintain atomicity between these (your reason wanting them 1 query), wrap them in transaction:

$db->begintransaction(); // perform insert // on success, perform delete // , commit transaction. $db->commit();