update two mysql tables with data from each

Question

Originally I had one mysql video table that is shown to a user in a simple html table. They could select one or more checkboxes ($video_list) to mark for deletion with a query (doesn't actually delete, but updates record)

//store all video names in array
    foreach ($video_list as $element) {
        $deleteNames[] = $db->quote($element);
    }

//create nice comma separated list for query
$deleteNames = implode(',', $deleteNames);

// query used
$query_delete_video = sprintf('UPDATE `videos` SET deleted_datetime=NOW() WHERE `video_name` IN (%s)', $deleteNames);

Now the problem is that this html table now has images as well, not just videos. These images are in a separate table called images. A user can select a number of videos or images at the same time or just videos or just images all for deletion.

So now the list $deleteNames has potentially a mix of videos and image names.

The query for images alone would look like:

$query_delete_image = sprintf('UPDATE `images` SET deleted_datetime=NOW() WHERE `image_name` IN (%s)', $deleteNames);

Same thing just in a different table and different column name. The only way I can think of having these two queries is that I separate $deleteNames into images and videos maybe by parsing the extension or something else.

Then I can run the query for each table. Is there a better way to tackle this, maybe in one query?

score 0 · Answer 1 · answered Jul 06 '13 at 01:36

I don't know the full table structure, so this may or may not work. But... why not use a table called "media" (or whatever you want), and add a "type" column which is indexed. You can then have your form have a checkboxes with the values of an auto increment id, and they would all be valid for that table. You could do the update query for both video/images with a single query, and your selects will just need an additional condition in WHERE, for "type='video'" or "type='image'"

Yes, I think a better structure would help, but I'm stuck with what I've got. Thanks. — Tom, Jul 06 '13 at 01:45

score 0 · Accepted Answer · edited May 23 '17 at 11:56

There is no need to do this in one query. I do not think you get any significant advantage by this. I think all you want to do is a clean, more efficient query. Having two queries to delete lists from two tables is not a bad design.

This relevant question might help you a little bit. MySql update two tables at once

Update query runs against a single table so I dont think you can update two tables in one shot.

update two mysql tables with data from each

2 Answers2