Insert conditional SQL string

Question

Is it possible to insert a new line if a condition occurs?

For example, I have this table without a primary key or uniqueness

+----------+--------+ | image_id | tag_id | +----------+--------+ | 39 | 8 | | 8 | 39 | | 5 | 11 | +----------+--------+ 

I would like to insert a line if the combination of image_id and tag_id does not exist for example

 INSERT ..... WHERE image_id!=39 AND tag_id!=8 

Answer 1

I think you are saying: you need to avoid duplicating rows in this table.

There are many ways to handle this. One of the easiest:

 INSERT INTO theTable (image_id, tag_id) VALUES (39, 8) WHERE NOT EXISTS (SELECT * FROM theTable WHERE image_id = 39 AND tag_id = 8) 

As @Henrik Opel noted, you can use a check constraint for combo columns, but then you should have a try / catch block somewhere else, which adds irrelevant complexity.

Edit to explain this comment ...

I guess this is a table comparing many-to-many relationships between movies and tags. I understand that you are probably using php, but I hope the C # pseudo-code below is clear enough anyway.

If I have a Movie class, the most natural way to add a tag is the AddTag() method:

 class Movie { public void AddTag(string tagname) { Tag mytag = new Tag(tagname); // creates new tag if needed JoinMovieToTag(this.id, mytag.id); } private void JoinMovieToTag(movie_id, tag_id) { /* database code to insert record into MovieTags goes here */ /* db connection happens here */ SqlCommand sqlCmd = new SqlCommand("INSERT INTO theTable... /* etc */"); /* if you have a check constraint on Movie/Tag, this will throw an exception if the row already exists */ cmd.ExecuteNonQuery(); } } 

There is no practical way to check for duplicates earlier in this process, because another user can tag the movie at any time, so there is no way around this.

Note. . If you try to insert an error record, then there is an error, so throwing an error is appropriate, but if not, you do not need additional complexity in the error handler.

Answer 2

Try using a database trigger to efficiently add rows to another table based on table updates.

Answer 3

I assume that you want to insert a row if it does not contain these values.

I don't have MySQL in front of me, but overall SQL should work:

 INSERT INTO a_table (image_id, tag_id) SELECT ? image_id, ? tag_id WHERE image_id!=39 AND tag_id!=8 

If you want to insert a line only if there is no such line at all, you can do this:

 INSERT INTO a_table (image_id, tag_id) SELECT ? image_id, ? tag_id WHERE not exists (SELECT 1 from a_table WHERE image_id!=39 AND tag_id!=8) 

Answer 4

If you are using InnoDB with transcoding, you can simply request the data first, and then paste in your code later if the rows are already found with the values. Also, add a unique constant across both columns and try pasting. If a failure occurs, if it already exists, which you can ignore. (This is less preferred than my first approach.)

Answer 5

Is Henrik Ope’s loan fee when he noticed that we all missed, including me, a simple unique restriction on two columns. Ultimately, this is the best solution.

Answer 6

 To avoid duplicate row insertion use the below mentioned query: INSERT INTO `tableName` ( `image_id`, `tag_id`) SELECT `image_id`, `tag_id` FROM `tableName` WHERE NOT EXISTS (SELECT 1 FROM `tableName` WHERE `image_id` = '39' AND `tag_id` = '8' );