首页 \ 问答 \ 在CTE中运行多个SET语句?(Running multiple SET statements in CTE?)

在CTE中运行多个SET语句?(Running multiple SET statements in CTE?)

使用SQL 2012

我有一个CTE声明给我错误的结果。 每个record_id的多个记录可能存在不同的类型。 这似乎是跳过记录而不是正确更新所有记录:

WITH cte as (
    SELECT
        o.sname, o.type, o.record_id, 
        p.data1, p.data2, p.data3

    FROM
        table1 p
        JOIN table2 o ON o.record_id = p.record_id
    WHERE
        o.record_id IN(1,2,3)
        --AND (o.type = 123 or o.type = 456 or o.type = 789)       

UPDATE cte
set data1 = (case when type = 123 then 1 else data1 end),
data2 = (case when type = 456 then 1 else data2 end),
data3 = (case when type = 378 then 1 else data3 end)
where type in (123,456,789) 

不知道为什么会这样。

我所追求的是仅查看某些记录,如果存在特定的TYPE值,则每次遇到特定TYPES时将DATA值更改为1。

如果我以这种方式运行CTE的UPDATE部分,它可以正常工作,而不是在一起时:

UPDATE cte
    set data1 = (case when type = 123 then 1 else data1 end),
    where type in (123) 

UPDATE cte
    set data2 = (case when type = 456 then 1 else data2 end)
    where type in (456)

UPDATE cte
    set data3 = (case when type = 789 then 1 else data3 end)
    where type in (789)

怎么了?

这是表格和期望的输出:

TABLE1      
record_id   |type   |sname
------------|-------|-----|
1           |123    |alpha
2           |123    |alpha
2           |456    |beta
3           |456    |beta
3           |789    |gamma

表2最初都是零

Desired Output:         
TABLE2          
record_id|  data1|  data2|  data3|
---------|-------|-------|-------|
1        |1      |   0   |   0
2        |1      |   1   |   0
3        |0      |   1   |   1

实际产量:

TABLE2          
record_id|  data1|  data2|  data3|
---------|-------|-------|-------|
1        |1      |   0   |   0
2        |1      |   0   |   0
3        |0      |   1   |   0

谢谢,

MP


using SQL 2012

I have a CTE statement that give me incorrect results. Multiple records for each record_id may exist with different types. This seems to be skipping records and not updating all of them correctly:

WITH cte as (
    SELECT
        o.sname, o.type, o.record_id, 
        p.data1, p.data2, p.data3

    FROM
        table1 p
        JOIN table2 o ON o.record_id = p.record_id
    WHERE
        o.record_id IN(1,2,3)
        --AND (o.type = 123 or o.type = 456 or o.type = 789)       

UPDATE cte
set data1 = (case when type = 123 then 1 else data1 end),
data2 = (case when type = 456 then 1 else data2 end),
data3 = (case when type = 378 then 1 else data3 end)
where type in (123,456,789) 

Not sure why this happens.

What I am after is to look at only certain records and if a specific TYPE value exists, change the DATA value to 1 every time it is encountered for specific TYPES.

If I run the UPDATE part of the CTE this way, it works correctly, just not when together:

UPDATE cte
    set data1 = (case when type = 123 then 1 else data1 end),
    where type in (123) 

UPDATE cte
    set data2 = (case when type = 456 then 1 else data2 end)
    where type in (456)

UPDATE cte
    set data3 = (case when type = 789 then 1 else data3 end)
    where type in (789)

Whats wrong?

Here are Tables and desired outputs:

TABLE1      
record_id   |type   |sname
------------|-------|-----|
1           |123    |alpha
2           |123    |alpha
2           |456    |beta
3           |456    |beta
3           |789    |gamma

Table 2 is originally all zeros

Desired Output:         
TABLE2          
record_id|  data1|  data2|  data3|
---------|-------|-------|-------|
1        |1      |   0   |   0
2        |1      |   1   |   0
3        |0      |   1   |   1

Actual Output:

TABLE2          
record_id|  data1|  data2|  data3|
---------|-------|-------|-------|
1        |1      |   0   |   0
2        |1      |   0   |   0
3        |0      |   1   |   0

Thanks,

MP


原文:https://stackoverflow.com/questions/42629031
更新时间:2020-03-27 09:40

最满意答案

您可以简单地在子查询中使用聚合来检查给定record_id存在哪种类型,然后像这样进行多表更新:

update t2
set t2.data1 = t1.data1,
    t2.data2 = t1.data2,
    t2.data3 = t1.data3
from table2 t2
join (
    select record_id,
        max(case when type = 123 then 1 else 0 end) as data1,
        max(case when type = 456 then 1 else 0 end) as data2,
        max(case when type = 789 then 1 else 0 end) as data3
    from table1
    group by record_id
    ) t1
    on t1.record_id = t2.record_id;

演示

另一种方法是使用与EXISTS相关性:

update t2
set data1 = case when exists (select 1 from table1 t1 where t1.record_id = t2.record_id and t1.type = 123) then 1 else 0 end,
    data2 = case when exists (select 1 from table1 t1 where t1.record_id = t2.record_id and t1.type = 456) then 1 else 0 end,
    data3 = case when exists (select 1 from table1 t1 where t1.record_id = t2.record_id and t1.type = 789) then 1 else 0 end
from table2 t2;

演示2


You can simply use aggregation in a subquery to check which type exists for a given record_id and then multitable update like this:

update t2
set t2.data1 = t1.data1,
    t2.data2 = t1.data2,
    t2.data3 = t1.data3
from table2 t2
join (
    select record_id,
        max(case when type = 123 then 1 else 0 end) as data1,
        max(case when type = 456 then 1 else 0 end) as data2,
        max(case when type = 789 then 1 else 0 end) as data3
    from table1
    group by record_id
    ) t1
    on t1.record_id = t2.record_id;

Demo

Another way is using correlation with EXISTS:

update t2
set data1 = case when exists (select 1 from table1 t1 where t1.record_id = t2.record_id and t1.type = 123) then 1 else 0 end,
    data2 = case when exists (select 1 from table1 t1 where t1.record_id = t2.record_id and t1.type = 456) then 1 else 0 end,
    data3 = case when exists (select 1 from table1 t1 where t1.record_id = t2.record_id and t1.type = 789) then 1 else 0 end
from table2 t2;

Demo 2

2017-03-06

相关文章

更多

最新问答

更多
  • css在元素之前中断列而不破坏包装器(css break column before element without breaking the wrapper)
  • 如何在Xamarin共享项目中使用自定义渲染器(How to use Custom Renderer in Xamarin Shared Project)
  • 如何为特定表中的特定字段设置唯一?(How to set unique for specific field from specific table?)
  • Google SDK iOS - sign()方法完成处理程序(Google SDK iOS - sign() method completion handler)
  • 在具有接口{}值的地图上实现String()(Implement String() on a map with interface{} values)
  • 检查数据库中是否已存在用户名(Check if username already exist in DB)
  • 使用javascript进行ajax调用时阻止用户交互(Block user interaction while doing ajax call using javascript)
  • 什么'if(err)'在Javascript中精确测试?(What does 'if (err)' tests precisely in Javascript?)
  • jQuery mouseleave无法正常工作(jQuery mouseleave not working)
  • 寻求使用的一些说明(Seeking some clarification on use of )
  • 将数组传递给注释的语法(syntax for passing array to annotation)
  • 用于从两个日期范围之间的文件中提取数据的Shell脚本(Shell script to extract data from file between two date ranges)
  • 元素隐藏但父()没有(Element hides but parent() not)
  • 如何使用Google App Engine Java平台开发web ui(How to develop web ui with Google App Engine Java platform)
  • 对于OWL A级;(For an OWL class A; Getting all properties that A is their domain)
  • Excel VBA公式格式问题(Excel VBA Formula Format Issue)
  • ORA - 02287序列号不允许在这里(ORA - 02287 sequence number not allowed here)
  • Github拉忽略特定文件(Github Pull Ignore Specific File)
  • SQL CONVERT函数在SQL Server中工作但不在应用程序中(SQL CONVERT function working in SQL Server but not in application)
  • backbone.js适用于大型应用程序(backbone.js for large applications)
  • 防止程序关闭(Preventing program from closing)
  • 生成不带图像的heightMap(Generating a heightMap without an Image)
  • Bootstrap - 如何将包含文本的div居中?(Bootstrap - How to center div that has text inside it?)
  • Android - 片段findViewById()总是null?(Android - Fragment findViewById() always null?)
  • 确定CSS中的高度(Figuring out heights in CSS)
  • 使用__autoload包含类和使用命名空间(Use __autoload to include class and use namespace)
  • setTimeout()不允许我传递文本值[重复](setTimeout() doesn't allow me to pass text values [duplicate])
  • 在NSUserDefault中恢复值(Restoring value in NSUserDefault)
  • 知道如何将这种下沉的悬停效果添加到图像/链接吗?(Any idea how to add this sinking hover effect to an image/link?)
  • 在XIB中淡入/淡出UISegmentedControl(fade in/fade out UISegmentedControl in XIB)