CODEWITHSUNDEEP
sqlmysqldatabase
tommizzle
tommizzle

Reputation: 1019

Inserting multiple rows with SQL where a record does not exist

I want to insert multiple rows of data into a MySQL database, but only when my order_id field is unique. This is the current query I have, which doesn't work. Lets say a record with an order_id of 2 is already in the table:

INSERT INTO conversion
       (user_id,url_id,order_id,sale,commission,transaction_date,process_date) 
VALUES (1,1,1,'32',0.3995,'2010-11-15 12:15:18','2010-11-15 12:15:18'),
       (3,6,2,'*not-available*',0.001975,'2010-11-15 12:15:18','2010-11-15 12:15:18') 
WHERE (order_id <> 3);

Any help is appreciated.

Tom

Upvotes: 1

Views: 2544

Answers (3)

Eric K Yung
Eric K Yung

Reputation: 1784

If I am in a similar situation, I would create a stored procedure to handle the logic of figuring out whether an order_id already exists.

--Run this first
--It will create a stored procedure call InsertConversion
--Begin of stored procedure
CREATE PROCEDURE InsertConversion 
    @user_id int,
    @url_id int,
    @order_id int,
    @sale varchar(5),
    @commission money,
    @transaction_date datetime,
    @process_date datetime
AS
BEGIN
    SET NOCOUNT ON;

    if not exists(select order_id from conversion where order_id = @order_id)
    begin
        INSERT INTO conversion(user_id, url_id, order_id, sale, commission, transaction_date, process_date)
        VALUES(@user_id, @url_id, @order_id, @sale, @commission, @transaction_date, @process_date)
    end
END
GO
--End of stored procedure

Once the store procedure created, you can execute it and pass in the same values as you would pass into an INSERT/VALUES statement:

exec InsertConversion 1,1,1,'32',0.3995,'2010-11-15 12:15:18','2010-11-15 12:15:18'
exec InsertConversion 3,6,2,'*not-available*',0.001975,'2010-11-15 12:15:18','2010-11-15 12:15:18'

If you want to be fancy, you can include a couple of 'print' statement in the store procedure to tell you whether it inserts the record.

Upvotes: 1

tommizzle
tommizzle

Reputation: 1019

Solved by using REPLACE.

Example:

REPLACE INTO conversion (user_id,url_id,order_id,sale,commission,transaction_date,process_date) VALUES (1,1,3,'32',0.3995,'2010-11-15 12:50:31','2010-11-15 12:50:31'),(1,2,2,'*not-available*',0.001975,'2010-11-15 12:50:31','2010-11-15 12:50:31');

url: http://dev.mysql.com/doc/refman/5.0/en/replace.html

Thanks all.

Upvotes: 2

Spudley
Spudley

Reputation: 168755

INSERT doesn't support the WHERE clause because if you're inserting it implies that the record doesn't currently exist, so therefore there would be nothing for the WHERE clause to look at.

The way to do it in the example you've given is simply not to call the INSERT statement if the order_id field in your insert doesn't match the criteria you want.

If you're calling INSERT multiple times, you'd have some sort of code (either SQL or an external program) which loops through the rows you're inserting; this would be where you'd filter it.

Upvotes: 1

Related Questions