homepage Welcome to WebmasterWorld Guest from 54.197.211.197
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

    
SQL update multiple tables
Readie

WebmasterWorld Senior Member



 
Msg#: 4128792 posted 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

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



 
Msg#: 4128792 posted 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;


dc

Readie

WebmasterWorld Senior Member



 
Msg#: 4128792 posted 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.

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved