Welcome to WebmasterWorld Guest from 107.20.34.173

Forum Moderators: open

Message Too Old, No Replies

SQL update multiple tables

     

Readie

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

WebmasterWorld Senior Member 5+ Year Member



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)

WebmasterWorld Senior Member dreamcatcher is a WebmasterWorld Top Contributor of All Time 10+ Year Member



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;


dc

Readie

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

WebmasterWorld Senior Member 5+ Year Member



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.
 

Featured Threads

Hot Threads This Week

Hot Threads This Month