blocks|key|4582239|text|你可以这样做：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|4582240|UPDATE+table_users
++++SET+cod_user+=+(case+when+user_role+=+'student'+then+'622057'
+++++++++++++++++++++++++when+user_role+=+'assistant'+then+'2913659'
+++++++++++++++++++++++++when+user_role+=+'admin'+then+'6160230'
++++++++++++++++++++end),
++++++++date+=+'12082014'
++++WHERE+user_role+in+('student',+'assistant',+'admin')+AND
++++++++++cod_office+=+'17389551';|code-block|syntax|javascript|4582241|我不明白你的日期格式。日期应使用本机日期和时间类型存储在数据库中。|4582242|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

You can do it this way:

<pre><code>UPDATE table_users
 SET cod_user = (case when user_role = 'student' then '622057'
 when user_role = 'assistant' then '2913659'
 when user_role = 'admin' then '6160230'
 end),
 date = '12082014'
 WHERE user_role in ('student', 'assistant', 'admin') AND
 cod_office = '17389551';
</code></pre>

I don't understand your date format. Dates should be stored in the database using native date and time types.

blocks|key|4582266|text|您可以使用CASE语句来处理多个if/then方案：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|4582267|UPDATE+table_to_update+
SET++cod_user=+CASE+WHEN+user_rol+=+'student'+THEN+'622057'
+++++++++++++++++++WHEN+user_rol+=+'assistant'+THEN+'2913659'
+++++++++++++++++++WHEN+user_rol+=+'admin'+THEN+'6160230'
+++++++++++++++END
++++,date+=+'12082014'
WHERE+user_rol+IN+('student','assistant','admin')
++AND+cod_office+=+'17389551';|code-block|syntax|javascript|4582268|entityMap^0|5|4|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@$9|N|A|O|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|P|8|@]|D|@]|E|$I|J]]|$1|K|3|-4|5|6|7|Q|8|@]|D|@]|E|$]]]|L|$]]

You can use a <code>CASE</code> statement to handle multiple if/then scenarios:

<pre><code>UPDATE table_to_update 
SET cod_user= CASE WHEN user_rol = 'student' THEN '622057'
 WHEN user_rol = 'assistant' THEN '2913659'
 WHEN user_rol = 'admin' THEN '6160230'
 END
 ,date = '12082014'
WHERE user_rol IN ('student','assistant','admin')
 AND cod_office = '17389551';
</code></pre>

blocks|key|75737|text|update+table_name
set+cod_user+=+
++++CASE+
++++WHEN+user_rol+=+'student'+THEN+'622057'
++++WHEN+user_rol+=+'assistant'+THEN+'2913659'
++++WHEN+user_rol+=+'admin'+THEN+'6160230'?
++++END,date+=+'12082014'

WHERE+user_rol+IN+('student','assistant','admin')
AND+cod_office+=+'17389551';|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|75738|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>update table_name
set cod_user = 
 CASE 
 WHEN user_rol = 'student' THEN '622057'
 WHEN user_rol = 'assistant' THEN '2913659'
 WHEN user_rol = 'admin' THEN '6160230'?
 END,date = '12082014'

WHERE user_rol IN ('student','assistant','admin')
AND cod_office = '17389551';
</code></pre>

blocks|key|4582334|text|我是这样做的：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|4582335|<update+id="updateSettings"+parameterType="PushSettings">
++++<foreach+collection="settings"+item="setting">
++++++++UPDATE+push_setting+SET+status+=+#{setting.status}
++++++++WHERE+type+=+#{setting.type}+AND+user_id+=+#{userId};
++++</foreach>
</update>|code-block|syntax|javascript|4582336|PushSettings在哪里|4582337|public+class+PushSettings+{

++++private+List<PushSetting>+settings;
++++private+String+userId;
}|4582338|它工作正常|4582339|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Q|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|R|8|@]|9|@]|A|$E|F]]|$1|K|3|L|5|6|7|S|8|@]|9|@]|A|$]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

I did it this way:

<pre><code>&lt;update id="updateSettings" parameterType="PushSettings"&gt;
 &lt;foreach collection="settings" item="setting"&gt;
 UPDATE push_setting SET status = #{setting.status}
 WHERE type = #{setting.type} AND user_id = #{userId};
 &lt;/foreach&gt;
&lt;/update&gt;
</code></pre>

where PushSettings is

<pre><code>public class PushSettings {

 private List&lt;PushSetting&gt; settings;
 private String userId;
}
</code></pre>

it works fine

blocks|key|4582362|text|UPDATE+Table1+SET+col1=+col2+FROM+(SELECT+col2,+col3+FROM+Table2)+as+newTbl+WHERE+col4=+col3|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|4582363|这里的col4和col1是在Table1中。col2和col3在Table2中|unstyled|4582364|我正在尝试更新每个col1，其中col4+=+col3为每行提供不同的值|4582365|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$B|C]]|$1|D|3|E|5|F|7|L|8|@]|9|@]|A|$]]|$1|G|3|H|5|F|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|F|7|N|8|@]|9|@]|A|$]]]|J|$]]

<pre><code>UPDATE Table1 SET col1= col2 FROM (SELECT col2, col3 FROM Table2) as newTbl WHERE col4= col3
</code></pre>

Here col4 &amp; col1 are in Table1. col2 &amp; col3 are in Table2 I Am trying to update each col1 where col4 = col3 different value for each row

I am trying to understand how to UPDATE multiple rows with different values and I just don't get it. The solution is everywhere but to me it looks difficult to understand.

For instance, three updates into 1 query:

<pre><code>UPDATE table_users
SET cod_user = '622057'
 , date = '12082014'
WHERE user_rol = 'student'
 AND cod_office = '17389551'; 

UPDATE table_users
SET cod_user = '2913659'
 , date = '12082014'
WHERE user_rol = 'assistant'
 AND cod_office = '17389551'; 

UPDATE table_users
SET cod_user = '6160230'
 , date = '12082014'
WHERE user_rol = 'admin'
 AND cod_office = '17389551'; 
</code></pre>

I <a href="http://www.karlrixon.co.uk/writing/update-multiple-rows-with-different-values-and-a-single-sql-query/" rel="noreferrer">read</a> an example, but I really don't understand how to make the query. i.e:

<pre><code>UPDATE table_to_update
SET cod_user= IF(cod_office = '17389551','622057','2913659','6160230')
 ,date = IF(cod_office = '17389551','12082014')
WHERE ?? IN (??) ;
</code></pre>

I'm not entirely clear how to do the query if there are multiple condition in the WHERE and in the IF condition..any ideas?

MySQL - UPDATE multiple rows with different values in one query

数据库

我试图理解如何用不同的值更新多个行，但我就是不明白。解决方案无处不在，但对我来说，它似乎很难理解。例如，3次更新为1个查询：UPDATE table_usersSET cod_user = '622057'    , date = '12082014'WHERE user_rol = 'student'    AND ...

问MySQL -在一个查询中使用不同的值更新多行
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问MySQL -在一个查询中使用不同的值更新多行EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问MySQL -在一个查询中使用不同的值更新多行
EN