英文:
SQLITE Update with conditions....case then?
问题
在此先感谢。我正在尝试使用另一个表中的日期更新表格。我已经成功完成了这一步，但现在我想仅在原始表格中的四个条件均为真时才更新每一行。
UPDATE newtable SET Serial = CASE WHEN (
(SELECT ETI FROM oldtable) != 0.0)
AND ((SELECT Serial FROM oldtable) != 0000)
AND ((SELECT Hardware FROM oldtable) != 0000)
AND ((SELECT Software FROM oldtable) != 0000)
THEN (SELECT Serial FROM oldtable WHERE newtable.IDENT = oldtable.IDENT)
END,
然而，此更新会影响所有行，因此这四个条件似乎不起作用。
感谢您的时间。
英文:
Thanks in advance.
I'm trying update a table with date of another table. I got this correctly, but now i want update each row only if 4 conditions are true in the original table.
UPDATE newtable SET Serial = CASE WHEN(
(SELECT ETI FROM oldtable) != 0.0)
AND ((SELECT Serial FROM oldtable) != 0000)
AND ((SELECT Hardware FROM oldtable) != 0000)
AND ((SELECT Software FROM oldtable) != 0000)
THEN (SELECT Serial FROM oldtable WHERE newtable.IDENT= oldtable.IDENT)END,
....
this update all the row, so the 4 conditions don't work.
Thanks for you time.
答案1
得分: 0
我认为你可以使用相关子查询来实现这个，就像这样：
update newtable
set serial = (
select serial
from oldtable
where ident = newtable.ident and serial <> 0 and hardware <> 0 and software <> 0 and eti <> 0
)
where exists (
select serial
from oldtable
where ident = newtable.ident and serial <> 0 and hardware <> 0 and software <> 0 and eti <> 0
)
很不清楚列 serial、hardware、software 等的数据类型是什么。我假设它们是数字（因为它们看起来像数字）。你可能需要根据你的确切用例进行适当的调整。
英文:
I think that you can do this with correlated subqueries, like so:
update newtable
set serial = (
select serial
from oldtable
where ident = newtable.ident and serial <> 0 and hardware <> 0 and software <> 0 and eti <> 0
)
where exists (
select serial
from oldtable
where ident = newtable.ident and serial <> 0 and hardware <> 0 and software <> 0 and eti <> 0
)
It is quite unclear what the datatypes of columns serial, hardware, software and so on is. I assumed they are numbers (because the look like numbers). You might need to adapt this to your exact use case.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论