Insert into Values with Where Clause

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.

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'

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)

Insert into ... values ( SELECT ... FROM ... )

Try:

INSERT INTO table1 ( column1 )
SELECT  col1
FROM    table2  

This is standard ANSI SQL and should work on any DBMS

It definitely works for:

• Oracle
• MS SQL Server
• MySQL
• Postgres
• SQLite v3
• Teradata
• DB2
• Sybase
• Vertica
• HSQLDB
• H2
• AWS RedShift
• SAP HANA
• Google Spanner

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.

Insert Into Table using Where Clause

How about something like this?

IF EXISTS (SELECT ID FROM Table2)
   Update Table2
   SET Table2.CURRENT_TIMESTAMP = Table1.CURRENT_TIMESTAMP  --However you want to update
   FROM Table2 tab2
   JOIN Table1 tab1 on Table1.ID = Table2.PTID
ELSE
   INSERT INTO Table2(ID, startTime)
   SELECT ID, CURRENT_TIMESTAMP
   FROM Table1

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';

How to INSERT INTO table(column) VALUES(value) WHERE column2 = value

The WHERE clause would make sense with an UPDATE:

UPDATE users
SET    time_in = $2
WHERE  username = $1;

Is that what you really want?

username should be defined UNIQUE for this - and probably in any case.

time_json should probably be type json or jsonb. time_in should probably be timestamptz.

An INSERT would not make any sense (apart from the invalid syntax), as you would have to fill in all NOT NULL columns without column default at the same time:

username VARCHAR(50) NOT NULL,
password VARCHAR(255) NOT NULL,
email VARCHAR(255) NOT NULL,

---

Related Topics