"SQL Tips" MySQL modification, dynamically specifies the field to be modified Update ' table ' Set (case time ...) = 1 WHERE id = XX

If you have entered this post, then you must have encountered the same problem as me. Do not look at the set case of the topic when ..., I started with the first reaction is to use case when but found not to work.

It's a little bit bigger than that: dynamic specifies the field to modify.

In fact, little white here did not find the solution I thought [laughter cry], but at least the problem is solved.

Here are the addresses of the original posts:

Http://stackoverflow.com/questions/4830191/t-sql-using-a-case-in-an-update-statement-to-update-certain-columns-depending-o

Here to record the method, in fact, you update all of the values you might want to update, when updating the value of the field to determine whether to update, do not update the original value is attached, updated with the new value. [Laugh and Cry] [Laugh and Cry] [Laugh and Cry] [Laugh and Cry] [Laugh and Cry]

The final finished SQL is as follows: (the requirement background is that when sender_id equals the userid passed in, the update send_status is 2; when receiver_id equals UserID, the update receiver_status is 2)

UPDATEmessageSETSend_status=( Case  whensender_id=#{userid, Jdbctype=BIGINT} Then 2 ELSESend_statusEND), Receive_status=( Case  whenreceiver_id=#{userid, Jdbctype=BIGINT} Then 2 ELSEReceive_statusEND)WHEREdialog_idinch (<foreach Collection="List" item="Item"Index="Index"Separator=",">#{item, Jdbctype=BIGINT}</Foreach>)

I use the MyBatis, batch modification.

At work, hoping to help people who are equally confused.

"SQL Tips" MySQL modification, dynamically specifies the field to be modified Update ' table ' Set (case time ...) = 1 WHERE id = XX