How to Insert with Where Clause

How to add a where clause in a MySQL Insert statement?

In an insert statement you wouldn't have an existing row to do a where claues on? You are inserting a new row, did you mean to do an update statment?

update users set username='JACK' and password='123' WHERE id='1';

SQL Server INSERT INTO with WHERE clause

I think you are trying to do an update statement (set amount = 12.33 for customer with ID = 145300)

UPDATE Payments
SET Amount = 12.33
WHERE CustomerID = '145300'

Else if you are trying to insert a new row then you have to use

IF NOT EXISTS(SELECT 1 FROM Payments WHERE CustomerID = '145300')
    INSERT INTO Payments(CustomerID,Amount)
    VALUES('145300',12.33)

Or if you want to combine both command (if customer exists do update else insert new row)

IF NOT EXISTS(SELECT 1 FROM Payments WHERE CustomerID = '145300')
    INSERT INTO Payments(CustomerID,Amount)
    VALUES('145300',12.33)
ELSE
    UPDATE Payments
    SET Amount = 12.33
    WHERE CustomerID = '145300'

MySQL direct INSERT INTO with WHERE clause

INSERT syntax cannot have WHERE clause. The only time you will find INSERT has WHERE clause is when you are using INSERT INTO...SELECT statement.

The first syntax is already correct.

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.

Update or Insert with where clause

I know you said you want a single query, but that's not possible if the User_Id column isn't unique. You can do it with the following two queries:

UPDATE yourTable
SET DataField1='wer', DataField2='try'
WHERE User_Id = 11;

INSERT INTO yourTable (DataField1, DataField2, User_Id)
SELECT 'wer', 'try', 11
FROM dual
WHERE NOT EXISTS (SELECT * FROM yourTable WHERE User_Id = 11)

The WHERE clause in the second query makes the SELECT query return an empty result when the user ID already exists in the table.

Oracle SQL - Insert Statement with Where Clause

I think you want update, not insert. The syntax is:

update mekka_h_o_a_fees
set money_handed_to_commity = 'Yes'
where month_year = 'July'

This sets column money_handed_to_commity to 'Yes' on the row(s) where column month_year has value 'July'.

insert into values with where clause

IF NOT EXISTS(SELECT 1 FROM [MyDB].[dbo].[Accounts] WHERE MyID = @MyID)
    INSERT INTO [MyDB].[dbo].[Accounts]
        (MyID, Phone, MyDate, Agent, Charge, Vendor)
        VALUES 
        (@MyID, @Phone, @MyDATE, @Agent, @Charge, @Vendor)

---

Related Topics