Welcome to the Treehouse Community
Want to collaborate on code errors? Have bugs you need feedback on? Looking for an extra set of eyes on your latest project? Get support with fellow developers, designers, and programmers of all backgrounds and skill levels here with the Treehouse Community! While you're at it, check out some resources Treehouse students have shared here.
Looking to learn something new?
Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and join thousands of Treehouse students and alumni in the community today.
Start your free trial
John Weland
42,478 Pointsupdating one column form a table with
So I have two tables one called user_info another called org.
I am trying to update user_info with information from org
UPDATE user_info
SET user_info.org_id = org.id
FROM user_info
JOIN org
ON user_info.organization = org.descrip
WHERE user_info.organization = 'ACME'
OR user_info.email_addr LIKE '%@acme.com'
AND org.descrip = 'ACME'
so basically I want to look at user_info and determine if a user there has ACME as an organization or @acme.com as an email
if that is true I want to go to the org table and find 'ACME' and get the id then update the column org_id in any of those rows in user_info with the id I retrieved form the org table
can anyone help?
2 Answers
John Weland
42,478 PointsUPDATE user_info
SET org_id = (select distinct id from org where descrip = 'ACME')
WHERE UPPER(organization) = 'ACME'
OR UPPER(email_addr) like '%@acme.com';
got it... man that took a few minutes!
working with a decade worth of data and trying to normalize everything that has been added over the years.
Andrew Chalkley
Treehouse Guest TeacherHave you got a local copy of the database? That's always a good idea to use to test before doing massive updates like this.
John Weland
42,478 PointsI don't but I have 30-40 clones of it we use them to dev on and they are all backed up. user_info is an exsisting table and we added org_id to it, org is a completely new table.