Update statement with joins

Question

I must create migration to update question image attribute but i need to check some attributes from other tables, my schema looks like this:

question {
id,
name,
groupId,
imageUrl -> i need to update this
}

group {
id, 
name, 
quizId
}

quiz {
id,
name, 
type -> where type is 'History'
}

And i need to update attribute imageUrl from table question WHERE quiz type is for example 'History', i'm not sure how i can join this tables when using UPDATE. I tried something like this but it's not working like i want.

UPDATE question SET image_url = 'pathToImage' FROM quiz q WHERE q.type = 'History'

Possible duplicate of [How to do an update + join in PostgreSQL?](http://stackoverflow.com/questions/7869592/how-to-do-an-update-join-in-postgresql) — McNets, Mar 29 '17 at 20:29
Already checked that, but i was not able to use answers from there :( — Sahbaz, Mar 29 '17 at 20:29
Can you please show me code example how to do it, im not experienced with postgress — Sahbaz, Mar 29 '17 at 20:32
Im aware of that, but i dont know how to join them when using update statement. — Sahbaz, Mar 29 '17 at 20:37
`question {` :: please don't invent pseudocode.Use real DDL, so that people understand you. — wildplasser, Mar 29 '17 at 21:16

score 1 · Accepted Answer · answered Mar 31 '17 at 17:59

So this is best solution that i came up with, and it worked perfectly for my migration.

UPDATE question SET image_url = 'https://path_to_image.com' WHERE id IN (SELECT q.id FROM question q
            JOIN group AS g ON q.groupi_id = g.id
            JOIN quiz AS qu ON qu.id = g.quiz_id
            WHERE qu.type = 'Lifestyle')

So basically i update image_url to all questions with ID's in sub query, and this is easiest way to do this.

McNets · Answer 2 · 2017-03-29T21:05:41.880

0

JOIN three tables on this way:

UPDATE question q 
SET    image_url = 'pathToImage'
FROM   (select g.Id 
        from group g
        JOIN   quiz z
        ON     g.quizID = z.Id
        WHERE  z.type = 'History') p
ON     q.groupId = p.Id;

edited Mar 29 '17 at 21:05

answered Mar 29 '17 at 20:34

McNets

10,352
3
32
61

I actually already have links to images, so i need to set that link, im not sure how and where i set image url with your example ? – Sahbaz Mar 29 '17 at 20:36
With your example im getting syntax error at or near "ON" (last ON where you say q.groupId = z.Id – Sahbaz Mar 29 '17 at 20:53
I cannot see pathToImage field on none of your tables. – McNets Mar 29 '17 at 21:02
Path to image is actually link that i want to set, its not attrbute its actually value for image_url – Sahbaz Mar 29 '17 at 21:04

score 0 · Answer 3 · answered Mar 29 '17 at 20:40

0

try:

   with cte as (
      select q.id qid
      from question q
      join "group" g on g.id = groupId
      join quiz z on z.id = quizId
      where type = 'History'
    )
    UPDATE question q
      SET image_url = 'pathToImage' 
    FROM cte.qid = q.id

answered Mar 29 '17 at 20:40

Vao Tsun

47,234
13
100
132

no - I didn't check it. is there an error?.. please update question with DDL and DML to reproduce your env – Vao Tsun Mar 29 '17 at 20:56

Update statement with joins

3 Answers3