代码之家 › 专栏 › 技术社区 › Rashmi Kini

SQL update查询通过连接3个表来获取值

sql-update join tsql sql-server sql

-1

Rashmi Kini · 技术社区 · 7 年前

请帮我写下面的更新查询。我正在使用SQL server 2014。

表A:

<table>
<tr>
<th>
Request Type | 
</th>
<th>Project ID  | </th>
<th>Latest |</th>
<th>	Link_ID</th>
</tr>
<tr>
-----------------------------------------------
</tr>
<tr>
<td>

a	
</td>
<td>1000</td>	<td>No</td>	<td>1</td>
</tr>
<tr>
<td>b</td>	<td>1005</td>	<td>Yes </td>      	<td>2</td>
<tr>
<td>123</td>	<td>1000</td><td>Yes   </td>    	<td>4</td></tr>
<tr>
<td>c	</td><td>1005</td>	<td>No</td>	<td>3</td></tr>
</table>

表B

<table>
<tr>
<th>
Question</th>	<th>Description</th>	<th>Answer_weightage</th>	<th>Link_ID
</th>

</tr>

<tr><td>
6	</td><td>Question6	</td><td>0</td>	<td>2</td>
</tr>
<tr>
<td>2</td>	<td>Question2</td>	<td>5</td>	<td>4</td></tr>
<tr>
<td>3</td>	<td>Question3	</td><td>5</td>	<td>4</td></tr>
<tr>
<td>4</td>	<td>Question4</td><td>	5</td>	<td>4</td></tr>
<tr>
<td>5	</td><td>Question5	</td><td>2</td>	<td>4</td></tr>
<tr>
<td>6</td>	<td>Question6	</td><td>2	</td><td>4</td></tr>
<tr>
<td>7</td>	<td>Question7</td>	<td>9	</td><td>4</td></tr>
<tr>
<td>1</td>	<td>Question1	</td><td>5</td>	<td>1</td></tr>
<tr>
<td>2</td>	<td>Question2	</td><td>9</td>	<td>1</td></tr>
<tr>
<td>3	</td><td>Question3	</td><td>5</td><td>	1</td></tr>
<tr>
<td>4</td>	<td>Question4</td>	<td>2	</td><td>1</td></tr>
<tr>
<td>5</td>	<td>Question5</td>	<td>5	</td><td>1</td></tr>
<tr>
<td>6</td>	<td>Question6</td>	<td>5	</td><td>1</td></tr>
<tr>
<td>7</td>	<td>Question7</td>	<td>2</td>	<td>1</td></tr>
<tr>
<td>1</td>	<td>Question1</td>	<td>2	</td><td>2</td></tr>
<tr>
<td>2</td>	<td>Question2</td>	<td>0	</td><td>2</td></tr>
<tr>
<td>3	</td><td>Question3</td>	<td>9	</td><td>2</td></tr>
<tr>
<td>4</td>	<td>Question4	</td><td>9</td><td>	2</td></tr>
<tr>
<td>5	</td><td>Question5	</td><td>9</td><td>	2</td></tr>
<tr>
<td>7	</td><td>Question7</td>	<td>5	</td><td>2</td></tr>
<tr>
<td>1</td>	<td>Question1</td><td>	2</td>	<td>4</td></tr>

</table>

表C:

<table>
<tr>
<th>Project ID</th>
<th>Question1</th>
<th>Question2</th>
<th>Question3</th>
<th>Question4</th>
<th>Question5</th>
<th>Question6</th>
<th>Question7</th>
</tr>
<tr>
<td>1000</td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
</tr>
<tr>
<td>1005</td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
</tr>
</table>

预期结果(见表C):

    <table>
    <tr>
    <th>Project ID</th>
    <th>Question1</th>
    <th>Question2</th>
    <th>Question3</th>
    <th>Question4</th>
    <th>Question5</th>
    <th>Question6</th>
    <th>Question7</th>
    </tr>
    <tr>
    <td>1000</td>
    <td>2</td>
    <td>5</td>
    <td>5</td>
    <td>5</td>
    <td>2</td>
    <td>2</td>
    <td>9</td>
    </tr>
    <tr>
    <td>1005</td>
    <td>2</td>
    <td>0</td>
    <td>9</td>
    <td>9</td>
    <td>0</td>
    <td>9</td>
    <td>5</td>
    </tr>
    </table>

1 回复 | 直到 7 年前

philipxy 7 年前

要更新表C的问题1,请将该值设置为从B中选择的Answer\u weightage,其中B.Description='question1'和linkId=(其中最新的链接Id='YES',并且与C具有相同的projectId)。

Update C set C.Question1 =
 (select Answer_weightage
    from B
    where Description='Question1' and Link_ID =
         (select Link_ID
            from A
            where A.ProjectID = C.ProjectID and Latest='Yes'))