How to use JOIN in UPDATE statement?

Question

Here is my query:

UPDATE reputations SET 
    type = new.type,
    score = new.score,
    qora = NOT (new.post_id = (SELECT t1.id
        FROM qanda t1 
        WHERE (EXISTS (SELECT 1 
            FROM qanda t2 
            WHERE ID = new.post_id 
                AND t1.ID = t2.related)
                OR t1.id = new.post_id)
                AND Type = 0)),
    question_id = (SELECT t1.id
        FROM qanda t1 
        WHERE (EXISTS (SELECT 1 
            FROM qanda t2 
            WHERE ID = new.post_id 
                AND t1.ID = t2.related)
                OR t1.id = new.post_id)
                AND Type = 0),
    post_id = new.post_id,
    table_code = new.table_code,
    comment_id = new.comment_id,
    owner_id = new.author_id,
    date_time = UNIX_TIMESTAMP()
WHERE events_table_id = old.id;

All I'm trying to do is remove one of those sub-queries, since both are identical. How can I do that?

I don't think your parentheses balance. – Gordon Linoff Jul 15 '17 at 20:22 — Gordon Linoff, Jul 15 '17 at 20:22
@GordonLinoff True ... I added one more. – Martin AJ Jul 16 '17 at 04:20 — Martin AJ, Jul 16 '17 at 04:20

score 2 · Accepted Answer · answered Jul 15 '17 at 19:54

2

In this case, I would DECLARE a local variable to store the result of the subquery. Read https://dev.mysql.com/doc/refman/5.7/en/stored-program-variables.html

Use the SELECT...INTO syntax to store the result of a query into a variable. Read https://dev.mysql.com/doc/refman/5.7/en/select-into.html

BEGIN
  DECLARE QANDA_ID INT;

  SELECT t1.id FROM qanda t1 
  WHERE (EXISTS (SELECT 1 FROM qanda t2 WHERE ID = new.post_id AND t1.ID = t2.related)
    OR t1.id = new.post_id) AND Type = 0
  INTO QANDA_ID;

  UPDATE reputations SET ...
    qora = not (new.post_id = QANDA_ID),
    question_id = QANDA_ID,
    ...

END

I made the variable name in all caps just to make it stand out in this example. But you can name it anything you want, it follows the same rules as other identifier names.

However, I suggest you don't name local variables the same as any of the column names in the table you use in the UPDATE statement. It gets confusing if you do that.

answered Jul 15 '17 at 19:54

Bill Karwin

538,548
86
673
828

Thank you .. upvote, just do you think which approach is better? either your *(using variable)* or Gordon's answer *(using `CROSS JOIN`)*? Noted that both work correctly. – Martin AJ Jul 16 '17 at 03:55
Both work fine, so I'd choose the one that is more straightforward and easier to code. – Bill Karwin Jul 16 '17 at 04:30
Can you please tell my why you not used `@` in the beginning of the variable name? Actually I'm confused, when exactly should I use `@` for variables in MySQL? – Martin AJ Aug 19 '17 at 15:34
@MartinAJ: https://stackoverflow.com/questions/1009954/mysql-variable-vs-variable-whats-the-difference – Bill Karwin Aug 19 '17 at 18:10

score 1 · Answer 2 · edited Jul 17 '17 at 11:12

1

It seems to me like you could use CROSS JOIN:

UPDATE reputations r CROSS JOIN
       (SELECT t1.id
        FROM qanda t1 
        WHERE (EXISTS (SELECT 1 
                       FROM qanda t2 
                       WHERE ID = new.post_id and t1.ID = t2.related
                      ) OR
               t1.id = new.post_id
              ) AND
              Type = 0
       ) t1
    SET r.type = new.type,
        r.score = new.score,
        r.qora = not (new.post_id = t1.id),
        r.question_id = t1.id,
        r.post_id = new.post_id,
        r.table_code = new.table_code,
        r.comment_id = new.comment_id,
        r.owner_id = new.author_id,
        r.date_time = UNIX_TIMESTAMP()
WHERE r.events_table_id = old.id;

edited Jul 17 '17 at 11:12

Martin AJ

6,261
8
53
111

answered Jul 15 '17 at 20:21

Gordon Linoff

1,242,037
58
646
786

Thank you .. upvote, just do you think which approach is better? either your *(using `CROSS JOIN`)* or Bill's answer *(using variable)*? Noted that both work correctly. – Martin AJ Jul 16 '17 at 04:21
1

@MartinAJ . . . I prefer to keep code in a single query where possible. Obviously, Bill has lots of valuable experience and good advice and his answers reflect that. The two alternatives are both very reasonable. – Gordon Linoff Jul 16 '17 at 13:25

How to use JOIN in UPDATE statement?

2 Answers2