Where Clause in Insert Statement Using MySQL/Php

Insert query with WHERE condition in mysql

IMPORTANT WARNING: mysql_query was deprecated in PHP 5.5.0, and it was removed in PHP 7.0.0. Instead, the MySQLi or PDO_MySQL extension should be used. See also MySQL: choosing an API guide and related FAQ for more information. Alternatives to this function include mysqli_query() and PDO::query()

FOR EDUCATIONAL PURPOSES: this is how your code should look if you are using mysql_query:

//you should definitely create a function that sanitizes the users input
//so that you don't get hacked via sql injection:
$value = sanitize($value);
$id= sanitize($id);

$sql = "UPDATE employee SET annual_additional = '$value' 
WHERE emp_id='$id'";
if (!result = mysql_query($sql))
{
    die("query error".mysql_error());
}

MySQL Insert query doesn't work with WHERE clause

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.

Using Multiple WHERE clause in an INSERT INTO ... SELECT statement

If the row in table1 already exists, you need an UPDATE .. JOIN statement instead of INSERT .. SELECT.

UPDATE table1 t1
JOIN table2 t2 ON t2.uid='userinput2'
SET t1.b_FName    = t2.FName,
    t1.b_Username = t2.Username,
    t1.b_PhoneNo  = t2.PhoneNo 
WHERE t1.uid='userinput1'

If you don't know if the row in table1 already exists, you can use an INSERT .. SELECT .. ON DUPLICATE KEY UPDATE statement:

INSERT INTO table1 (uid, b_FName, b_Username, b_PhoneNo)
    SELECT 'userinput1', FName, Username, PhoneNo
    FROM table2
    WHERE uid = 'userinput2'
    ON DUPLICATE KEY UPDATE
    SET b_FName    = VALUES(FName),
        b_Username = VALUES(Username),
        b_PhoneNo  = VALUES(PhoneNo)

Note that uid sould be primary keys or at least unique in both tables.

Mysql Insert query with multiple where and join operations

Finally this one works @shudder thanks for the help

INSERT INTO public_holidays (user_id, department_id,designation_id,date_cur,clock_in,clock_out)
SELECT cl.user_id, des.department_id , us.designation_id, cl.date,cl.clock_in, cl.clock_out 

FROM clock cl 

INNER JOIN holidays AS hol ON hol.date = cl.date

INNER JOIN users AS us ON cl.user_id = us.id

INNER JOIN designations AS des ON des.id = us.designation_id

WHERE date(cl.created_at) = '2016-06-13'

AND TIMESTAMPDIFF(second,cl.clock_in, cl.clock_out) = 28800;

---

Related Topics