使用MySQL Workbench更新同一列中的多个值 [英] Using MySQL Workbench to update multiple values in the same columns
问题描述
我需要运行mysql查询来更新stratodesk数据库.
I need to run a mysql query to update a stratodesk database.
我要实现的目标: 我需要通过stratodesk更新一堆瘦客户机PC，不幸的是没有大量更新可以做到这一点，所以我直接使用MySQL数据库.目标是更新所使用的映像和更新模式.
What I'm trying to achieve: I need to update a whole bunch of thin client PC's through stratodesk, unfortunately there is no bulk update to do this so i am working directly with the MySQL database. The goal is to update the image used and the update mode.
我有两个MySQL查询来执行此操作，其中一个将更新映像本身，另一个将更新映像更新模式".
I've got two MySQL queries to do this, one of which will update the image itself the other will update the "image update mode".
如果我运行第一个查询，它将更新图像，但是如果然后运行第二个查询以更新图像更新模式，它将撤消第一个查询.我的计划是将两个查询组合在一起并立即更新两个值.
if i run the first query it will update the image but if then run the second query to update the image update mode it will undo the first query. My plan was to combine both queries together and update both values at once.
我做了一些谷歌搜索，很多例子都像这样(我知道这个例子并不反映我正在使用的数据库结构).
I've done a bit of googling and a lot of the examples go something like this (I'm aware this example does not reflect the database structure I'm working with).
UPDATE orders SET
listPrice = 0,
bloggerPrice = 0.00,
customerPrice = 0.00
WHERE
orders.id = 245745
来自此堆栈溢出主题
现在，这给我的印象是，其中的每个值都作为单独的列存储在表"orders"中.我很确定，如果stratodesk的数据库遵循类似的模式，那将是可行的，但事实并非如此.
Now, this gives me the impression that each of the values there are stored as an individual colomn in the table "orders". I'm pretty certain this would work if the database for stratodesk followed a similar pattern but it doesnt.
它们将所有这些存储在名为"CONFIGVALUE"的表中-该表具有两列.我要更新的值是"CODE"和"VAL".
They store all this in a table called "CONFIGVALUE" - This has two columns. "CODE" and "VAL" which is where the values i want to update are.
我需要更新 a)图片
SET CONFIGVALUE.CODE = 'IMAGE',CONFIGVALUE.VAL="2.40.4130d-EEs-k305-181109"
b)公告模式
SET CONFIGVALUE.CODE = "IMAGE_UPDATE_MODE", CONFIGVALUE.VAL=2
正如我所说，如果我每次都运行，那么它将更新数据库.但是，第二个查询似乎撤消了第一个查询.
As i say, if i run each then it does update the database. But, the second query appears to undo the first query.
我将他们合并在一起，认为这可以解决我的问题，但事实并非如此.如果我将"image"的值括在单引号中，则会收到有关截断错误双打的错误.如果我使用双引号，查询将更新"image_update_mode"，但对"IMAGE"值不执行任何操作.
I've combined them together thinking it would solve my issue, but it does not. If i wrap the value for "image" in single quotes, i get an error about truncating incorrect doubles. If i use double quotes, the query will update the "image_update_mode" but does nothing to the "IMAGE" value.
组合查询:
UPDATE IGNORE CONFIGVALUE JOIN CONFIGOBJECT on CONFIGOBJECT.COID=CONFIGVALUE.COID JOIN CLIENTSTATE on CONFIGOBJECT.COID=CLIENTSTATE.COID JOIN STATUSVALUE on CLIENTSTATE.EID=STATUSVALUE.EID SET CONFIGVALUE.CODE = 'IMAGE', CONFIGVALUE.VAL='2.40.4130d-EEs-k305-181109', CONFIGVALUE.CODE = 'IMAGE_UPDATE_MODE', CONFIGVALUE.VAL=2 WHERE CONFIGOBJECT.PARENT=1221 AND CONFIGOBJECT.COTYPE=3 /*AND STATUSVALUE.VAL="HP t510 Thin Client"*/ AND CONFIGOBJECT.COID=895;
我已经使自己确信很多问题是由于值都存储在同一列中.在我看来，这些值应该有自己的列(可能是自己的表).
I've pretty much convinced myself a lot of the issue is coming from the fact the values are both stored in the same columns. In my mind, these values should have their own columns (possibly their own tables).
第二个问题，基于StratoDesk数据库布局，看起来是否存在某种规格化?我一直在努力解决这个问题.我觉得如果有的话，做起来会容易得多，或者至少我看过的例子更有意义.
A secondary question, based on the StratoDesk database layout does it look like there is any kind of normalisation? I'm stil trying to get my head around that. I feel it would be much easier to do if they were, or at least the examples I've seen would make more sense.
有人知道我如何做到这一点吗?还是过去有人尝试过类似的方法?如果有人有，查询看起来是否正确，或者有更干净的方法来做到这一点?我有几千个瘦客户端要更新，所以我希望在开始更新所有瘦客户端之前使这100％正常工作！
Does anybody know how i can achieve this? Or has anybody tried anything like this in the past? If anyone has, does the query look right or is there a cleaner way to do this? I've got a few thousand thin clients to update so would like to get this 100% working before i start updating all the thin clients!
此致
Philb
推荐答案
如果要更新一行中另一行的值等于给定值的列，则应使用WHERE子句.
If you want to update a column in a row where the values of another row quals a given value you should use a WHERE clause.
要更新图像:
UPDATE configvalue
SET val = '2.40.4130d-EEs-k305-181109'
WHERE code = 'IMAGE';
要更新图像更新模式:
UPDATE configvalue
SET val = '2'
WHERE code = 'IMAGE_UPDATE_MODE';
在没有WHERE的情况下，您将在所有行中更改val.
Without a WHERE you change the val in all rows.
如果您非常希望将其作为一条语句，则可以使用CASE表达式，如果代码与该值所针对的代码匹配，则返回一个新值，否则返回旧值.
If you desperately want to have it as one statement you can use a CASE expression that returns the new value if the code matches the code the value is for and otherwise just the old value.
UPDATE configvalue
SET val = CASE code
WHEN 'IMAGE' THEN
'2.40.4130d-EEs-k305-181109'
WHEN 'IMAGE_UPDATE_MODE' THEN
'2'
ELSE
val
END
WHERE code IN ('IMAGE',
'IMAGE_UPDATE_MODE');
这篇关于使用MySQL Workbench更新同一列中的多个值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
