0

I want to update the content of a table calls VIDEO_TAGS which contains 3 columns, fk_video_id, principal (not too important), and tag_value.

I want to do an update as follows,

"UPDATE VIDEO_TAGS 
SET tag_value= :newTag 
where tag_value= :oldTag 
  and fk_video_id = (SELECT FROM VIDEO_TAGS fk_video_id where tag_value= :productName)"

but it is clear that the sub-query will return many elements, while I only need one element -more than one row returned by a subquery used as an expression ERROR-. my question is how to edit the sub-query to get the one element that I need? Thank you

Improve this question
3
  • I suggest to start by adding video_id to (SELECT FROM VIDEO_TAGS, and use (SELECT video_id FROM VIDEO_TAGS (or at least the field that has the id for the video you are looking for) Commented Jun 19, 2022 at 16:39
  • To get max 1 row from a sub-query, you can use LIMIT. But that also needs an ORDER BY, see Postgres LIMIT/OFFSET strange behaviour Commented Jun 19, 2022 at 16:43
  • If multiple videos have the same productName, how do you want to choose just one? The first, the last, which one? (It sounds more likely that you need to update all of those videos? If so, use fk_video_id IN (sub-query) instead.) Commented Jun 19, 2022 at 16:51
Related questions
9
Select For Update statement in PostgreSql
2
PostgreSQL update query
0
postgresql update where like other table
Load 7 more related questions Show fewer related questions

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.