CODEWITHSUNDEEP
mysqlsql
Shai Zarzewski
Shai Zarzewski

Reputation: 1698

insert into table only if not exist on other table

I'm trying to do the following thing: I have two tables: ReportImage (imageId, reportId, counter) and userReportedImages (imageId, userId)

I want that every user will be able to report an image only once - this means that first I want to check if there is a row in 'userReportedImages' with the values (imageId, userId) if so do nothing, else create a row in 'ReportImage' with the values (imageId, reportId, counter), if such row already exist (other user reported that image) then I want to raise the counter.

so far before checking for same user report I had the following statement:

INSERT INTO ReportImage VALUES (imageId,reportId,1) ON DUPLICATE KEY UPDATE counter = counter+1

this statement is working fine.

I tried to change this statement to first check if the row exist on the other table, but I didn't manage to do it, can you help me?

Upvotes: 3

Views: 2417

Answers (3)

John Woo
John Woo

Reputation: 263693

First, you need to define a UNIQUE constraint or a compund column primary key on table ReportImage,

ALTER TABLE ReportImage ADD CONTRAINT tb_uq UNIQUE(ImageID, ReportID)

Give this a try,

INSERT INTO ReportImage(ImageID, ReportID, Counter)
SELECT  'imageID HERE' AS ImageID,
        'userID HERE' AS ReportID,
        1 AS Counter
FROM    userReportedImages a
        LEFT JOIN ReportImage b
            ON  a.imageId = b.imageId AND
                a.userId = b.ReportID AND
                a.imageID = 'imageID HERE' AND
                a.userID = 'userID HERE'
WHERE   b.imageId IS NULL OR 
        b.ReportID IS NULL
ON DUPLICATE KEY UPDATE counter = VALUES(counter) + 1

Upvotes: 3

Joseph Caracuel
Joseph Caracuel

Reputation: 974

you could try using NOT EXISTS

insert into table2(`name`)
select * from (select 'name1' as name) tmp
where not exists(
  select ('x') from table1 where name = 'test1'
  );

SQL Fiddle

Upvotes: 1

www
www

Reputation: 4391

An insert trigger should be a solution for you: link

Upvotes: 0

Related Questions