带Join的TSQL Update语句 [英] TSQL Update statement with Join

问题描述

我有两个具有父/子关系的表.我想用子项中的数据更新父项.但是，假设有2个孩子，我希望能够根据该孩子的其他某些列来选择使用哪个孩子进行更新. 这是我到目前为止的内容: 家长:@test 孩子:@exdat 预期结果，更新后的父项结果应仅包含大写字母.我想用孩子的一些数据更新父对象，但是如果存在多个，我宁愿选择一个孩子，其子代的p1大于p2，p2大于p3，p3大于p4

I have two tables with a parent/child relationship. I want to update the parent with data from the child. However assuming there are 2 children I would like to be able to pick which child is used for the update depending on some other column from the child. Here is what I have so far: Parent: @test Child: @exdat Expected outcome, result of parent after update should contain only Capital letters. I want to update the parent with some data from the child, but if more than one exists I'd rather choose a child with a dimp of p1 over p2, p2 over p3 and p3 over p4

DECLARE @test TABLE
(
  id  int,
  val char(1)
);
DECLARE @exdat TABLE
(
  id  int,
  dval char(1),
  dimp char(2)
);
INSERT INTO @test (id,val)
            SELECT 1,'a'
    UNION ALL   SELECT 2,'b'
    UNION ALL   SELECT 3,'c'
    UNION ALL   SELECT 4,'d'
    UNION ALL   SELECT 5,'e'
    UNION ALL   SELECT 6,'f'
    UNION ALL   SELECT 7,'g'
;
INSERT INTO @exdat (id,dval,dimp)
            SELECT 1,'A','p1'
    UNION ALL   SELECT 2,'B','p3'
    UNION ALL   SELECT 3,'C','p1'
    UNION ALL   SELECT 4,'D','p2'
    UNION ALL   SELECT 5,'E','p2'
    UNION ALL   SELECT 6,'F','p3'
    UNION ALL   SELECT 7,'w','p2'
    UNION ALL   SELECT 7,'g','p3'
    UNION ALL   SELECT 7,'G','p1'
    UNION ALL   SELECT 7,'z','p4'
;
UPDATE @test SET
    val = e.dval
FROM
    @test t
    INNER JOIN @exdat e ON t.id = e.id 
;
SELECT * FROM @test;

Result:
1   A
2   B
3   C
4   D
5   E
6   F
7   w <-- problem illustrated here 

此"w"可以是w，g，G，z中的任何一个.所以我问我如何才能基于其他一些列来优先选择子级?

This "w" could have been any of the values w,g,G,z. So I am asking how can I prioritize the child selection based on some other column ?

推荐答案

您想要执行的代替子查询的是子查询.像这样:

What you want to do instead of a join is a subquery. Something like this:

UPDATE
   a
SET
   a.val = ISNULL((
      SELECT TOP 1 x.dval
      FROM @exdat x
      WHERE x.id = a.id
      ORDER BY x.magic_field -- <- here's how you specify precedence
   ), 'ReasonableDefault')
FROM
   @test a

这篇关于带Join的TSQL Update语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！