title: SQL query for update/replace updating too many records
body: <p>I am a SQL novice and was &quot;nominated&quot; to work on a project to update some account settings for customers.<br />
  Basically I need to replace a setting (&quot;OLDACCESS&quot;) with a &quot;NEWACCESS&quot; on a user_settings table.  However, I only want to update/replace &quot;OLDACCESS&quot; with &quot;NEWACCESS&quot; for a subset of our users (users at Company ID 10 that have &quot;OLDACCESS&quot;.  All other users that have the &quot;OLDACCESS&quot; should be unchanged.</p>
  <p>The access settings are stored in a user_settings table, and the user's account profile info such as company ID is stored in a soft_users table.  Both tables are in the same database.</p>
  <p>Luckily we have a testing database that I can practice on before making the change in our production system.  I came up with the query below to do the update (from OLDACCESS to NEWACCESS) using a replace and then joining a &quot;soft_users&quot; table to allow me to do a a where clause to find users with company id 10 that have OLDACCEES.  The issue is my script is updating all users (with any company ID) with OLDACCESS to the NEWACCESS, not just users in company 10 that have the OLDACCESS. :[</p>
  <p>It's almost like the query is doing the update/replace but totally ignoring the join and where sections.  Pretty sure I am doing something totally in line with my newbie-ness.  Can someone help me out with tips to show where I am going wrong?</p>
  <pre><code>update user_settings
  set access = replace(user_settings.access::varchar, 'OLDACCESS'::varchar, 'NEWACCESS'::varchar)
  from soft_users
  join user_settings us
  on us.user_id = soft_users.user_id
  where soft_users.company_id = '10' and us.access like 'OLDACCESS%'
  </code></pre>
  <p>Note: not sure if it matters but the database is PostgreSQL.</p>
  <p>Thanks in advance,
  Mark</p>