UPDATE with JOIN between 3 Tables

[PriteshP23]

I have 3 tables as mentioned below:

Users
Code  Name    NW     London
 1   Bill     ----    ----  

Contacts
Code Country
  1       USA

Location
Country City
USA       NW

I need to update Users.NW = 1 in the Users Table. There are other cities like NW, London, Paris etc. In respective cities i need to update 1 and rest should be 0.

So, expected output
1 Bill 1 0
2 Steve 0 1

I hope i am clear to you. Thanks in advanced. This query is not working:

UPDATE Users SET NW='1' WHERE
(SELECT Users.Code, Contacts.code FROM Contacts 
LEFT JOIN ON Users.code = Contacts.code AND
INNER JOIN ON Contacts.Country = Location.Country 
 ) AND Users.NW = Location.City;

[adam_k]

Try this:

update Users set NW ='1' 
from Contacts left join Users
on Users.code = Contacts.code 
inner join Contacts.Country = Location.Country 
where Users.NW = Location.City

But be warned that it might contain errors or other typos, as it was written in the reply box and hasn't been tested.

Good luck

Edit: Just saw that this is in the MySQL subforum. The update supports from in MS SQL, but don't know what MySQL will do.

[PriteshP23]

@adam_k

Thanks for your help. But it is not working. There is an error on second line "FROM Contacts.. " .

[PriteshP23]

I did it..!! :):)

UPDATE Users SET NW='1' WHERE Code
                (SELECT code FROM Contacts 
                INNER JOIN ON Users.code = Contacts.code 
                WHERE Users.NW = Location.City);