如何使用select查询更新列 [英] How to update column using the select query

问题描述

大家好，



我想使用select查询来更新列，例如，

Hi All,

I want to update the column using the select query like,

 Select distinct
 Case when PRDDIM.INS_PROD_TYPE_TXT=’Diablility income’
Then Update clinet_pay_test set
 DI=’Y’
END CASE
 FROM EBISDWP.OPER_PRODCUT_DIM PRDDIM

在上面的查询中，client_pay_test是表名，DI是列名。我们检查PROD_type_txt值是否等于残疾收入值如果是，那么在client_pay_test表中我们将DI列设置为y值。在运行此查询时，我在Disability Income附近收到语法错误。你能否帮我解决这个语法问题或任何其他解决方案。



ps：我们无法使用更新查询，我们只能通过Select查询来实现。

Here in the above query client_pay_test is the table name and DI is the column name.we are checking the PROD_type_txt value equal to disability income value If it is true then in the client_pay_test table we are setting the DI column to y value. While running this query I am getting syntax error near 'Disability Income'. Could you please help me for resolving this syntax issue or any other solution.

ps : We cannot achieve using the update query, we can do only by the Select query.

Select distinct
Case when PRDDIM.INS_PROD_TYPE_TXT=’Diablility income’
Then Update clinet_pay_test set
DI=’Y’;
Life =’N’;
Annuity=’N’
Case when PRDDIM.INS_PROD_TYPE_TXT=’Life’
Then Update clinet_pay_test set
DI=’N’;
Life =’Y’;
Annuity=’N’
Case when PRDDIM.INS_PROD_TYPE_TXT=’Annuity’
Then Update clinet_pay_test set
DI=’N’;
Life =’n’;
Annuity=’Y’
END CASE
FROM EBISDWP.OPER_PRODCUT_DIM PRDDIM

解释：



所以表名是clinet_pay_test它有很多列，暂时我们可以告诉它有DI，Life和Annuity是表中的列。



例如：



如果PRDDIM.INS_PROD_TYPE_TXT ='Diablility income'，那么我们必须将DI列值设置为'y'，将其他2列（生命和年金）设置为'N '。相似

如果PRDDIM.INS_PROD_TYPE_TXT ='Life'，那么我们必须将LIFE列值设置为'y'，将其他2列（DI和Annuity）设置为'N'。

如果PRDDIM.INS_PROD_TYPE_TXT ='Life'，那么我们必须将Annuity列值设置为'y'，将其他2列（DI和Life）设置为'N'。



希望很清楚。



我尝试了什么：



我尝试了上面提到的查询

Explanantion:

So the table Name is clinet_pay_test It has many column,for time being we can tell there are DI, Life and Annuity are the column in the table.

For example :

If the PRDDIM.INS_PROD_TYPE_TXT=’Diablility income’ then we have to set the DI column value to ‘y’ and other 2 columns (life and Annuity) to ‘N’.Similarly
If the PRDDIM.INS_PROD_TYPE_TXT=’Life’ then we have to set the LIFE column value to ‘y’ and other 2 columns (DI and Annuity) to ‘N’.
If the PRDDIM.INS_PROD_TYPE_TXT=’Life’ then we have to set the Annuity column value to ‘y’ and other 2 columns (DI and Life) to ‘N’.

Hope it is clear.

What I have tried:

I tried the query which I mentioned above

推荐答案

你需要以某种方式加入这两个表 - 它是从你的代码片段中不清楚两个表是如何相关的。



然后你需要做一个类似这样的更新声明...

You need to join the two tables somehow - it's not clear from your code snippet how the two tables are related.

Then you need to do an update statement similar to this ...

Update clinet_pay_test set
DI = CASE WHEN PRDDIM.INS_PROD_TYPE_TXT='Diablility income' THEN 'Y'
		  WHEN PRDDIM.INS_PROD_TYPE_TXT='Life' THEN 'N'
		  WHEN PRDDIM.INS_PROD_TYPE_TXT='Annuity' THEN 'N'
	 END,
Life = CASE WHEN PRDDIM.INS_PROD_TYPE_TXT='Diablility income' THEN 'N'
		    WHEN PRDDIM.INS_PROD_TYPE_TXT='Life' THEN 'Y'
		    WHEN PRDDIM.INS_PROD_TYPE_TXT='Annuity' THEN 'n'
	 END, 
Annuity = CASE WHEN PRDDIM.INS_PROD_TYPE_TXT='Diablility income' THEN 'N'
			   WHEN PRDDIM.INS_PROD_TYPE_TXT='Life' THEN 'N'
			   WHEN PRDDIM.INS_PROD_TYPE_TXT='Annuity' THEN 'Y'
	 END
FROM EBISDWP.OPER_PRODCUT_DIM PRDDIM
INNER JOIN clinet_pay_test ON --- insert how the tables are linked here

您需要完成上面的ON子句并添加任何WHERE您可能需要的条款。

抱歉，我无法测试此特定查询

You will need to complete the ON clause above and add any WHERE clause that you may need.
Sorry I have not been able to test this specific query

这篇关于如何使用select查询更新列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！