MySQL: Updating and Selecting (Sub-Query)

A neat trick I didn’t know (I’m more PHP than MySQL, but always wanting to simplify my PHP code):

UPDATE `table1` SET `counter`=(SELECT COUNT(*) FROM `table2`)  WHERE `id`={$id}

If the things to count in table2 are specific, you can use WHERE in the sub-query as well:

UPDATE `table1` SET `counter`=(SELECT COUNT(*) FROM `table2` WHERE `id`={$id})  WHERE `id`={$id}

As you can see, it’s much easier than turning to PHP and then back to MySQL. Not saying you have to do everything in one side or the other, but it’s nice to know when certain things can be done with either. ;-)

Side Note: I use the backwards tilde (`) for databases, tables, and fields (this is important, as sometimes we use names that might mean something for the database engine!).
I also use upper-case for SQL commands, as it’s easier to read and it separates SQL from PHP in projects.

Tags:

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: