Turn SQL select into an update statement

alexisbr
alexisbr used Ask the Experts™
on
Hi.  I am having a brain freeze this morning.  Can anyone please help me turn the sql statement below into an Update statement so programs.degree_id (new field) ends up with the value in degrees.degree_id below?  The select query works and shows me the correct value in degrees.degree_id.

Thanks so much,
Alexis
SELECT program_id, degrees.degree_id, programs.degree_id
FROM degrees, programs
WHERE programs.short_degree_name = degrees.short_degree_name
AND programs.short_degree_level = degrees.short_degree_level
AND programs.degree_type = degrees.degree_type

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Oracle dba
Top Expert 2009
Commented:
update programs
set degree_id = (SELECT a.degree_id
  FROM degrees a
  WHERE programs.short_degree_name = a.short_degree_name
  AND programs.short_degree_level = a.short_degree_level
  AND programs.degree_type = a.degree_type)

UPDATE d SET d.degree_id = p.degree_id FROM degrees d INNER JOIN programs
p ON p.short_degree_name = d.short_degree_name
AND p.short_degree_level = d.short_degree_level
AND p.degree_type = d.degree_type

Open in new window

Guy Hengel [angelIII / a3]Billing Engineer
Most Valuable Expert 2014
Top Expert 2009

Commented:
article for that:
http://www.experts-exchange.com/articles/Database/Miscellaneous/UPDATES-with-JOIN-for-everybody.html

Open in new window

OWASP: Avoiding Hacker Tricks

Learn to build secure applications from the mindset of the hacker and avoid being exploited.

update programs p , degrees a
set p.degree_id = a.degree_id
  WHERE p.short_degree_name = a.short_degree_name
  AND p.short_degree_level = a.short_degree_level
  AND p.degree_type = a.degree_type

try this
or...
UPDATE degrees SET degree_id = p.degree_id FROM degrees d INNER JOIN programs
p ON p.short_degree_name = d.short_degree_name
AND p.short_degree_level = d.short_degree_level
AND p.degree_type = d.degree_type

Open in new window

Commented:
UPDATE
   programs
SET
   programs.degree_id = degrees.degree_id
FROM
   programs
INNER JOIN
   degrees
ON
   programs.short_degree_name = degrees.short_degree_name
AND
   programs.short_degree_level = degrees.short_degree_level
AND
   programs.degree_type = degrees.degree_type

Author

Commented:
Thanks, everyone.  The first reply that was posted worked like a charm so I am using that.  Thanks Geert_Gruwez:.  I was so close.  I had everything but didn't put it in the subselect.  I will remember that for the next time.

Regards,
Alexis

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial