CODEWITHSUNDEEP
mysqlsql
guagay_wk
guagay_wk

Reputation: 28030

How to make this repetitive Update statement in MySQL shorter?

I have this Update statement in MySQL which updates multiple columns using select statements.

UPDATE TableA
SET ColumnA =
(
    SELECT TableB.ColumnA
    FROM TableB
    WHERE (TableB.ColumnX = 0 AND TableB.id = TableA.fk_id)
)
,ColumnB =
(
    SELECT TableB.ColumnB
    FROM TableB
    WHERE (TableB.ColumnX = 0 AND TableB.id = TableA.fk_id)
)
,ColumnC =
(
    SELECT TableB.ColumnC
    FROM TableB
    WHERE (TableB.ColumnX = 0 AND TableB.id = TableA.fk_id)
)

It works fine and is simple to understand. However, it violates the DRY (don't repeat yourself) principle. The WHERE clause repeats. It does not look good in a code review exercise. How to rewrite this MySQL statement in a manner which does not violate the DRY principle?

Upvotes: 0

Views: 49

Answers (1)

Multisync
Multisync

Reputation: 8797

UPDATE TableA a INNER JOIN TableB b
       ON b.ColumnX = 0 AND b.id = a.fk_id
   SET a.ColumnA = b.ColumnA, a.ColumnB = b.ColumnB, a.ColumnC = b.ColumnC;

MySQL update syntax allows to combine mutiple tables: http://dev.mysql.com/doc/refman/5.0/en/update.html

Upvotes: 2

Related Questions