How to use both SELECT and UPDATE statements in one query?

Question

I have this update query which works as well:

UPDATE tbname t CROSS JOIN ( SELECT related FROM tbname WHERE id = 5 ) x
    SET AcceptedAnswer = ( id = 5 )
  WHERE t.related   = x.related

I also have two select statements which validates somethings. Actually I want to check these to conditions before updating:

Condition1:

(SELECT 1 FROM tbname 
  WHERE id = x.related AND
        author_id = 29
)

Condition2:

(SELECT 1 FROM tbname
  WHERE id = x.related AND
       (
          ( amount IS NOT NULL AND
            NOT EXISTS ( SELECT 1 FROM tbname
                           WHERE related = x.related AND
                                 AcceptedAnswer = 1 )
          ) OR amount IS NULL
       )
)

How can I combine those two conditions with that updating query?

Here is what I've tried so far but it doesn't work and throws this error:

UPDATE tbname CROSS JOIN ( SELECT related FROM tbname WHERE id = 5 ) x
SET AcceptedAnswer = ( id = 5 )
  WHERE q.related   = x.related
    AND
    (SELECT 1 FROM tbname 
      WHERE id = x.related AND
            author_id = 29
    ) AND
    (SELECT 1 FROM tbname
      WHERE id = x.related AND
            (
              ( amount IS NOT NULL AND
                NOT EXISTS ( SELECT 1 FROM tbname
                               WHERE related = x.related AND
                                     AcceptedAnswer = 1 )
              ) OR amount IS NULL
            )
    )

#1093 - You can't specify target table 'tbname' for update in FROM clause

Maybe subqueries? Or you could resolve it via triggers that check your conditions, or maybe even a stored procedure... — Mondongo, May 11 '16 at 15:59
check this out ... http://stackoverflow.com/questions/2334712/update-from-select-using-sql-server — Lalit Mehra, May 11 '16 at 16:01

ScaisEdge · Answer 1 · 2016-05-11T16:53:50.610

1

Seems your update is equivalent to this

update tbname as a
inner join tbname as b on a.related = b.related and b.id = 5
set AcceptedAnswer = (id =  5)

your query seem set to true (1) the AccepetdAnswer of the row with id = 5 for the row that have acceppeted equalt to the accepted value of th row with id = 5 (false / 0) in the other case ..

for test use

select * from  tbname as a
inner join tbname as b on a.related = b.related and b.id = 5
and (b.related = a.id and a.author_id = 29)
and (b.related = a.id and 
          (a.amont is not null and (a.related = b.related and a.AcceptedAnswer = 1)))

edited May 11 '16 at 16:53

answered May 11 '16 at 16:16

ScaisEdge

131,976
10
91
107

Your simple update query isn't the same as mine. Mine sets both `1` for accepted answers and `0` for other answers of the question. *(for changing accepted answer or even undo it)* ..! btw my question is about implementing two conditions before updating. – Shafizadeh May 11 '16 at 16:23
Twell i removed the second conclusion and i think the first is right ...this is just for rewrite the firts part of your query removing subselect .. – ScaisEdge May 11 '16 at 16:25
Yes, using an `inner join` instead of `cross join` seems correct in your solution. Just may you please tell me how can I add those two conditions in the query? I want if those tow conditions return `1` then updating applies otherwise updating fails. – Shafizadeh May 11 '16 at 16:30
how the two condition are related to the firts part of the query ? – ScaisEdge May 11 '16 at 16:34
The firts mean and the id equals to x.related and author_id = 29? – ScaisEdge May 11 '16 at 16:35
[This](http://stackoverflow.com/questions/37160002/how-do-i-check-a-few-conditions-before-updating) is all I'm trying to do. – Shafizadeh May 11 '16 at 16:38
***#1054** - Unknown column 'a.aumont' in 'on clause'* – Shafizadeh May 11 '16 at 16:43
***#1052** - Column 'AcceptedAnswer' in on clause is ambiguous*. I can debug this, But I want you do that, because that's your query and you know what you have done so far ..! – Shafizadeh May 11 '16 at 16:52
Well not there isn't any error, but it doesn't match any row `:-)`. Anyway thanks for your try and showing me how can I do that by joining. upvote – Shafizadeh May 11 '16 at 16:55

score 0 · Answer 2 · answered May 11 '16 at 16:14

I'm not pretty sure what is the purpose of the SET clause (id =5) anyway this way avoids the use of the cross join provided that you don't use the table "x" to get something beyond the "related" items.

UPDATE tbname 
SET 
    AcceptedAnswer = ( id = 5 )
WHERE  
    #THIS IS EQUIVALENT TO THE JOIN CLAUSE
    id IN ( SELECT related FROM tbname WHERE id = 5 )

    #THIS IS THE CONDITION 1 POINTING tnname    
    AND author_id = 29  

    #THIS IS THE CONDITION 2 POINTING tbname    
    AND ( 
     ( amount IS NOT NULL 
       AND NOT AcceptedAnswer = 1
     ) OR amount IS NOT NULL 
    )
;

How to use both SELECT and UPDATE statements in one query?

2 Answers2