SQL- how to process a subquery returning more than one value

Hi

Im tryin to select a number of PK's of a table where a FK is of each row has to be derived using another query.
The following wont work as the subquery is returning a number of rows/values, so how do I get around this??

select user_lesson_id from TAB_user_lesson where user_course_id = (SELECT user_course_id FROM TAB_user_course WHERE course_id = 1) ' this select could return a number of values

any ideas please
louise_8Asked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
If you want to update when joining to another table:

UPDATE TAB_user_lesson
SET field = 3
FROM TAB_user_lesson L
JOIN TAB_user_course C
ON C.user_course_id = L.user_course_id
AND C.course_id = 1

CHeers
0
 
Ryan ChongCommented:
Try:

select user_lesson_id from TAB_user_lesson where user_course_id IN (SELECT user_course_id FROM TAB_user_course
WHERE course_id = 1) ' this select could return a number of values

Cheers
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
If the subselect is expected to return many rows, IN should be replaced by a JOIN:

select d.user_lesson_id
from TAB_user_lesson L
JOIN TAB_user_course C
ON C.user_course_id = L.user_course_id
AND C.course_id = 1

CHeers
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Ryan ChongCommented:
> If the subselect is expected to return many rows, IN should be replaced by a JOIN
Agree with angellll :)
0
 
louise_8Author Commented:
thanks angellll, just one more query could I use an Update in this way and how?
eg update user_lesson set field = 3 where user_lesson.user_course_id = user_course.user_course_id and etc..  

or is the only way to do the slect first and get the pk's then update using the pk??
0
 
louise_8Author Commented:
Brilliant, thanks a million

Louise
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.