Asked  6 Months ago    Answers:  5   Viewed   21 times

What's wrong with this query:

INSERT INTO Users( weight, desiredWeight ) VALUES ( 160, 145 ) WHERE id = 1;

It works without the WHERE clause. I've seemed to have forgot my SQL.

 Answers

45

MySQL INSERT Syntax does not support the WHERE clause so your query as it stands will fail. Assuming your id column is unique or primary key:

If you're trying to insert a new row with ID 1 you should be using:

INSERT INTO Users(id, weight, desiredWeight) VALUES(1, 160, 145);

If you're trying to change the weight/desiredWeight values for an existing row with ID 1 you should be using:

UPDATE Users SET weight = 160, desiredWeight = 145 WHERE id = 1;

If you want you can also use INSERT .. ON DUPLICATE KEY syntax like so:

INSERT INTO Users (id, weight, desiredWeight) VALUES(1, 160, 145) ON DUPLICATE KEY UPDATE weight=160, desiredWeight=145

OR even like so:

INSERT INTO Users SET id=1, weight=160, desiredWeight=145 ON DUPLICATE KEY UPDATE weight=160, desiredWeight=145

It's also important to note that if your id column is an autoincrement column then you might as well omit it from your INSERT all together and let mysql increment it as normal.

Tuesday, June 1, 2021
 
mario
answered 6 Months ago
74

From the PHP Manual on mysqli_stmt::execute:

mysqli_stmt::execute -- mysqli_stmt_executeExecutes a prepared Query

Returns TRUE on success or FALSE on failure.


if ($stmt->execute()) { // exactly like this!
    $success = true;
}

You're doing it right... What's your dilemma?

Wednesday, March 31, 2021
 
Sauleil
answered 9 Months ago
77

You cannot use a table name as a parameter.

If the point of this is to create several tables with the same structure but different name, I suggest using something like:

$table_names = array('a', 'b', 'c');

foreach($table_names as $name) {
  $query = "CREATE TABLE `$name` (id INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(id))";
  // run query or add it to a collection to run later
  // or append a ';' to the end of the string and do it with a multi_query
}
Saturday, May 29, 2021
 
Pegues
answered 7 Months ago
66

make two dumps or if you dont want to make two dumps then try two command

a.

mysqldump -h<hostname> -u<username> -p 
<databasename>  <table1> <table2> <table3>
--single-transaction --no-create-info > dumpfile.sql

b.

mysqldump -h<hostname> -u<username> -p <databasename> 
<table4> --where 'created > DATE_SUB(now(), INTERVAL 7 DAY)',
<table5> --where 'created > DATE_SUB(now(), INTERVAL 7 DAY)
--single-transaction --no-create-info >> dumpfile.sql

c.

gzip dumpfile.sql
Monday, August 2, 2021
 
ClydeFrog
answered 4 Months ago
13

You may need to use quotes:

mysqldump -t -u root -p  mytestdb mytable --where="datetime LIKE '2014-09%'"
Monday, August 9, 2021
 
CKing
answered 4 Months ago
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :
 
Share