SQL update multiple tables

Forum Moderators: open

Message Too Old, No Replies

SQL update multiple tables

Readie

1:39 pm on May 7, 2010 (gmt 0)

Right, I'm trying to update two tables:

jobs
regions

And I am trying to update the same column in both (jobs.greater_region_id, regions.greater_region_id) to the same value, based on them both having the same value in (jobs.region_id, regions.region_id).

Now, I know this could be done with two UPDATE querys, but I want to learn how to do this.

The only thing I cannot work out here is the WHERE clause:

UPDATE regions, jobs SET regions.greater_region_id = "", jobs.greater_region_id = "" WHERE regions.region_id = "" AND jobs.region_id = ""
----
UPDATE regions, jobs SET regions.greater_region_id = "", jobs.greater_region_id = "" WHERE regions.region_id = "" OR jobs.region_id = ""

So, my question: do I need to use AND or OR here? Or, (and I don't know if this is valid SQL)
WHERE jobs.region_id, regions.region_id = ""
?

dreamcatcher

3:15 pm on May 7, 2010 (gmt 0)

Hi Readie,

The AND clause will be what you are looking for. OR would update based on one or the other field evaluating to true. For an update based on both values being the same you`ll need AND.

You can also do it like this, which isn`t much different:

UPDATE regions, jobs SET regions.greater_region_id = '', jobs.greater_region_id = '' WHERE regions.region_id = 'blah' AND regions.region_id = jobs.region_id;

Readie

4:09 pm on May 7, 2010 (gmt 0)

Thanks dream :)

I just have a logical issue with this sort of thing - I suppose it's a lot simpler if you imply the logic of a join here though.