How can I make an UPDATE query on INNER JOIN using OR

Question

I want to update the values of one column in a table from '0' to '1', if either of four values in columns in another table are '1'. Somehow this doesn't seem to work and I was just wondering if anyone could help me get the code right or find a different way of doing it if it's not possible,

mysql_query("UPDATE members 
            INNER JOIN forum_banners ON members.id = forum_banners.userid
            SET members.beta = '1' WHERE forum_banners.bebeta = '1' OR 
            forum_banners.bibeta = '1' OR forum_banners.cbeta = '1' OR 
            forum_banners.wbeta = '1'") or die(mysql_error());

That's what I tried, but it's not working, I suspect because of the OR. I tried having all updatings in different mysql_query bits, but that didn't work either.

Possible Duplicate: http://stackoverflow.com/questions/982919/sql-update-query-using-joins — mclark1129, Feb 09 '12 at 22:09
Your query looks like it should work to me... in what way is it not working? Are you sure that there are rows in `forum_banners` that meet this criteria? — Michael Fredrickson, Feb 09 '12 at 23:25

score 0 · Accepted Answer · answered Feb 09 '12 at 22:04

You should be able to update from multiple table references. This is untested, but gives you an idea:

UPDATE 
    members, forum_banners 
SET 
    members.beta = '1'
WHERE 
    members.id = forum_banners.userid 
    AND forum_banners.bebeta = '1' 
    OR forum_banners.bibeta = '1' 
    OR forum_banners.cbeta = '1' 
    OR forum_banners.wbeta = '1'

http://dev.mysql.com/doc/refman/5.0/en/update.html

Note "Multi-Table syntax"

score 0 · Answer 2 · answered Feb 09 '12 at 22:09

0

Try

UPDATE
    m
SET 
    m.beta = '1'
FROM 
    members m
INNER JOIN 
    forum_banners fb
    ON m.id = fb.userid
WHERE 
    fb.bebeta = '1' 
    OR fb.bibeta = '1' 
    OR fb.cbeta = '1' 
    OR fb.wbeta = '1'"

Aliases also help make your syntax a little neater.

answered Feb 09 '12 at 22:09

mclark1129

7,532
5
48
84

1

This is perfect for other flavors of SQL, but MySql has a different syntax for multiple tables in an update statement. – Michael Fredrickson Feb 09 '12 at 23:21

How can I make an UPDATE query on INNER JOIN using OR

2 Answers2