CODEWITHSUNDEEP
mysqlsqlsubquery
Shafizadeh
Shafizadeh

Reputation: 10340

How to use sub-query result twice in the query?

I have this query:

UPDATE QandA SET accepted = IF ( id <> :answer_id, 0, 1 )
  WHERE col1 = ( SELECT related FROM QandA WHERE id = :answer_id ) AND
        col2 = ( SELECT related FROM QandA WHERE id = :answer_id )

As you see there is two identical sub-queries. How can I write that sub-query once and use its result twice?

Upvotes: 2

Views: 1491

Answers (2)

jarlh
jarlh

Reputation: 44776

No need for two sub-queries here, one is enough, just make sure col1 = col2 and you're done:

UPDATE QandA SET accepted = IF ( id <> :answer_id, 0, 1 )
  WHERE col1 = ( SELECT related FROM QandA WHERE id = :answer_id ) AND
        col2 = col1

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269953

Use can use CROSS JOIN:

UPDATE QandA CROSS JOIN
       ( SELECT related FROM QandA WHERE id = :answer_id ) x
    SET accepted = ( id = :answer_id)
WHERE col1 =  x.related AND col2 = x.related;

Note that I also removed the if(). This is unnecessary in MySQL where boolean expressions are treated as integers in a numeric context.

An alternative would use IN:

WHERE ( SELECT related FROM QandA WHERE id = :answer_id ) IN (col1, col2)

Upvotes: 3

Related Questions