INSERT INTO where not duplicate specific columns

Question

I have an insert that looks like this (using MySQL)

INSERT INTO user_actions(action_id,user_id,action_type,record_id,on_table) VALUES
(
    action_id,
    '1',
    '1',
    '32',
    '1'
)
ON DUPLICATE KEY UPDATE 
user_id=user_id AND 
action_type = action_type AND 
record_id = record_id AND 
on_table = on_table

and action_id is AUTO_INCREMENT

The objective si to insert into the table, only if certain values are different.

What is my mistake??

And second, could i do an IF, ELSE with an select? like

var_count = count
(
    SELECT action_id FROM user_actions
    WHERE 
    action_id = 'value' AND
    user_id 'value' AND
    action_type 'value'
    record_id 'value' AND
    on_table 'value' AND
)

IF var_count = 0

    INSERT INTO user_actions(action_id,user_id,action_type,record_id,on_table) VALUES
    (
        action_id,
        '1',
        '1',
        '32',
        '1'
    )
    
ELSE

    ... echo error
    
END IF

score 1 · Accepted Answer · edited May 23 '17 at 12:21

1

You need to use , instead of AND :

ALTER IGNORE TABLE user_actions ADD UNIQUE KEY ix1(user_id,action_type,record_id,on_table);

see here

INSERT INTO user_actions(action_id,user_id,action_type,record_id,on_table) VALUES
(
 action_id,
 '1',
 '1',
 '32',
 '1'
)
ON DUPLICATE KEY UPDATE 
user_id=user_id,
action_type = action_type,
record_id = record_id,
on_table = on_table;

for other SELECT method you need to write those statements inside stored procedure.

CREATE PROCEDURE sp_check_user_actions(arg_action_id INT)
BEGIN
    SELECT count(1)
    INTO @var_count
    FROM user_actions
    WHERE
    action_id = arg_action_id AND
    user_id 'value' AND
    action_type 'value'
    record_id 'value' AND
    on_table 'value';

    IF(@var_count = 0)
    THEN

        INSERT INTO user_actions(action_id,user_id,action_type,record_id,on_table) VALUES
        (
            arg_action_id,
            '1',
            '1',
            '32',
            '1'
        );
    ELSE
        SELECT "Error: Duplicate user action" As error_message;
    END IF;
END;

edited May 23 '17 at 12:21

Community

1
1

answered Aug 02 '12 at 10:10

Omesh

27,801
6
42
51

so whenever i want to use IF, ELSE, i have to create this "PROCEDURE" ? it is just like a function in PHP for me – Master345 Aug 02 '12 at 10:27
yes something like this I have updated my post for you. and then call this procedure as call sp_check_user_actions(1234); – Omesh Aug 02 '12 at 10:34
i appreciate your kindness, but the first one(as far as i tryied, it doesn't work, it keeps inserting rows, the only difference between them is that they have different autoincrement action_id and date-timestamp .. what can i do? – Master345 Aug 02 '12 at 10:39
in order to use ON DUPLCIATE KEY UPDATE you need to have unique index on table. – Omesh Aug 02 '12 at 10:42
the same result .... the only unique key in my table is the action_id, witch is autoincrement ... if you have 2 seconds, look into my table please http://codepad.org/Q72j5SbY thanks – Master345 Aug 02 '12 at 10:53
you can add multiple unique keys on table. See my updated post for adding UNQIUE key. – Omesh Aug 02 '12 at 11:16
and what does it means "unique key" ? means that only one record_id might be into the database? because there can be more actions on the same record_id but by different users, do you understand me? – Master345 Aug 02 '12 at 11:22
unique key means there can be only one row in table for a single value. no duplicate values for combination of columns specified in unique key. – Omesh Aug 02 '12 at 11:29
i understand now! it is referring in a row situation, not just a single value, for that what it is called, DUPLICATE value? or what – Master345 Aug 02 '12 at 11:35

INSERT INTO where not duplicate specific columns

1 Answers1