Will this sql code update correctly?

I want to update only one field in a mysql table.

I have an "ad_id" which is unique开发者_JAVA百科.

The field "mod_date" is a TIMESTAMPS field, which is the one I need to update.

UPDATE main_table
SET main_table.mod_date = NOW()
WHERE classified.ad_id = $ad_id";

I haven't tested this yet because I am afraid it might update all rows.

So I have two questions:

Is there anyway to prevent MySql to update more than 1 row?

Is this sql code correct for updating one row only?

Thanks

If ad_id is unique, it will only update one row (if $ad_id is valid, zero otherwise).

If your worried about an update like this, rewrite it as a select to confirm which rows it will operate on before running it.

Your query doesn't look like it would work as such because it checks for field ad_id in table classified which hasn't been defined in the statement. If this is just a partial query and you're joining the classified table somewhere in the query there's not enough info here to tell how many rows will be modified.

You can add LIMIT 1 to the end of the query to make it update only the first row the query finds, but if you're not sure what the query does the first row might not be the one you want to modify.

As a side note I do have to say that if you're afraid to try and see what the query does, it means that either you don't have a backup of the database or you're working directly with a production database, and both of those options sound pretty scary.

What's the relation between main_table and classified?

For example...

UPDATE header h
   INNER JOIN detail d ON d.id_header = h.id_header
SET h.name = 'New name'
WHERE d.id_detail = 10

will update name in the header table for specific id_detail.

In your case if ad_id is unique then you can be sure that MySQL will update only one row.