Insert Using 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.

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

insert into from where condition on both tables

You might use a FROM-clause (with JOINs) in your INSERT..SELECT queries, but also in your UPDATE and/or DELETE queries.

If you want to insert new records into table1 based on data from table2, you may only need to make sure that there will not be attempted to insert data in table1 that might break any constraints.

The WHERE-clause in your example INSERT-query might not be valid in that case, since it seems to assume that there are already matching records in table1. (There might be possible exceptional scenarios that would justify such a query, but I will not elaborate on those scenarios here, since your question does not indicate that that's relevant in your case.)

Example for inserting records in table1 based on (related) data in table1 and table2:

INSERT INTO table1 (name)
SELECT table2.name
FROM table2 JOIN table1 ON table1.[ref] = table2.[ref] --use some sensible relation logic between table1 and table2 here
WHERE ... --check for valid data here

If you want to update existing records in table1 with data from table2, you can probably use an UPDATE query instead of an INSERT-query. Again, you should check that you do not attempt to update data that would break any constraints.

Example for updating records in table1 that match with data in table2:

UPDATE table1
SET name = table2.name
FROM table2 
WHERE table2.id = table1.id --or use some other sensible relation logic between table1 and table2 here (together with other validation logic)

You might also use a FROM-clause in your UPDATE query, but it seems you must be careful not to include the target table in that FROM-clause and supply the correct join with the target table records in the WHERE-clause.

How to use INSERT statement with WITH clause?

You need a formal SELECT inside the EXISTS clause in the 4th example:

WITH temp AS (SELECT 1 FROM organization_member WHERE user='628111234567' AND role='0')

SELECT 1 WHERE EXISTS (SELECT 1 FROM temp);

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

---

Related Topics