asked on

Turn SQL select into an update statement

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

ASKER CERTIFIED SOLUTION

Geert G

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

bmatumbura

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]

article for that:

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

Open in new window

shru_0409

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

bmatumbura

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

mabbj747

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

alexisbr

ASKER

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