查询根据条件更新列 [英] Query to update a column based on a condition
问题描述
我每天都会收到以下格式的文件: -
CID Mobile FirstName Lastname UpdateIdentifier
第一次文件将以这种方式包含记录: -
CID Mobile FirstName Lastname UpdateIdentifier
1 999999 ABC PQR N
此记录将插入两个主表中,例如TableA和表B.
下次再来一个文件。但这次数据如下: -
CID Mobile FirstName Lastname UpdateIdentifier
1 999999 ABCD PQR U
这次记录将在表格和表格b中更新(即,firstname将从abc更改为abcd,更新itentifier将从N到U)
一旦UpdateIdentifier首次从N更改为U,我们必须在其中插入一个值为10的列。
必需输出如下: -
CID Mobile FirstName姓氏UpdateIdentifier积分
1 999999 ABCD PQR U 10
下次每当Updateitenfier更改为U时,我们应该允许其他列数据更新但是points列应该没有改变,因为我们只需要在第一次更改为U时需要多点。
请帮助。
我尝试了什么:
我正在考虑采用标志变量在这两个表中以及第一次当updateitenfier是U increement时它是1.
下次每当数据到来时检查标志值。如果它是一个不发布点。
请帮助代码。
你是通过使用两个表过度复杂的情况,你没有给我们表格模式没有帮助。但是,也许这会对你有所帮助......
考虑这两个表...create table demoInput
(
CID int ,
Mobile varchar ( 30 ),
FirstName varchar ( 30 ),
姓氏 varchar ( 30 )
)
创建 表 demoTarget
(
CID int ,
Mobile varchar ( 30 ),
FirstName varchar ( 30 ),
姓氏 varchar ( 30 ),
UpdateIdentifierchar ( 1 ),
Points int
)你如何创建
demoInput
取决于你...它可能是存储过程或实际的输入参数表。我在第一次运行时使用了这个示例数据INSERT INTO demoInput(CID) ,Mobile,FirstName,LastName) values
( 1 ,' 12345',' Joe',' Bloggs'),
( 2 ,' 34567',' Jane',' Bloggs')
如果以下代码 in 存储的过程(比如)< pre lang = <跨度cl ass =code-string> SQL> - 先做更新,否则你会处理输入两次!
更新 T SET
Mobile = I.Mobile,UpdateIdentifier = ' U',
- 这一位判断这是否是第一次更新
Points = CASE WHEN 点 IS NULL < span class =code-keyword> THEN 10 ELSE 积分结束,
- 此位是否为后续更新
FirstName = CASE WHEN 积分 IS NULL THEN T.FirstName ELSE I.FirstName 结束,
姓氏= CASE WHEN 积分 IS NULL 那么 T.Lastname ELSE I.Lastname END
FROM demoInput I
left 外部 join demoTarget T ON I.CID = T.CID
WHERE T.CID IS 不 NULL
- 然后插入任何新内容
INSERT INTO demoTarget
SELECT I.CID,I.Mobile,I.FirstName,I.Lastname,' N' < span class =code-keyword> AS UpdateIdentifier,CAST( NULL as int ) AS 点
FROM demoInput我
左 外 加入 demoTarget T ON I.CID = T.CID
WHERE T.CID IS NULL然后我相信你会得到b你想要的行为。
首先使用上述数据运行:1 12345 Joe Bloggs N NULL
2 34567 Jane Bloggs N NULL
第二次运行TRUNCATE TABLE demoInput
INSERT INTO demoInput(CID,Mobile, FirstName,LastName) values
( 1 , ' 12345999',' Joe',' Bloggs'),
( 2 ,' 34567999', ' 简',' Bloggs'< /跨度> ),
( 3 ,' 3435566' ,' New',' 人')
1 12345999 Joe Bloggs U 0
2 34567999 Jane Bloggs U 0
3 3435566新人N NULL然后第三次运行
TRUNCATE 表 demoInput
INSERT INTO demoInput(CID,Mobile,FirstName ,LastName)值
( 1 ,' 12345999',' Joe2',' Bloggs'),
( 2 ,' 34567998',' Jane',' Bloggs2'),
( 3 ,' 3435566',' New',' Person3')给出
1 12345999 Joe2 Bloggs U 10
2 34567998 Jane Bloggs2 U 10
3 3435566 New Person U 10
可能会有一些皱纹,你的两个目标表需要排序,但这应该让你知道如何去做它
Everyday I will receive file in the below format:-
CID Mobile FirstName Lastname UpdateIdentifier
First time the file will contain record in this fashion:-
CID Mobile FirstName Lastname UpdateIdentifier
1 999999 ABC PQR N
This record will be inserted in two main tables say TableA and table B.
Next time another file will come. But this time the data will be like below:-
CID Mobile FirstName Lastname UpdateIdentifier
1 999999 ABCD PQR U
This time the record will be updated in tablea and table b.(i.e. firstname will change from abc to abcd and update itentifier will change from N to U)
Once the UpdateIdentifier has got changed from N to U for the first time we have to insert a column Points with value as 10 in it.
Required Output will be below:-
CID Mobile FirstName Lastname UpdateIdentifier Points
1 999999 ABCD PQR U 10
Next time whenever the Updateitenfier is changed to U we should allow the other columns data to be updated but the points column should not get changed since we need to alot points only at first time when it changes to U.
Please Help.
What I have tried:
I am thinking of taking a flag variable in both the tables and at first time when the updateitenfier is U increement it to 1.
next time whenever the data is coming check the flag value. if it is one do not post point.
Please help with the code.
You are over-complicating the situation by using two tables and you haven't helped by not giving us the table schema. However, perhaps this will help you...
Consider these two tables...create table demoInput ( CID int, Mobile varchar(30), FirstName varchar(30), Lastname varchar(30) ) create table demoTarget ( CID int, Mobile varchar(30), FirstName varchar(30), Lastname varchar(30), UpdateIdentifier char(1), Points int )How you create
demoInput
is up to you... it could be input parameters to a stored procedure or an actual table. I used this sample data on my first runINSERT INTO demoInput (CID, Mobile, FirstName, LastName) values (1, '12345','Joe','Bloggs'), (2, '34567','Jane','Bloggs') If the following code is in a stored procedure (say) <pre lang="SQL">-- Do the updates first, otherwise you will process the input twice! UPDATE T SET Mobile = I.Mobile, UpdateIdentifier = 'U', -- This bit works out whether this is the first Update Points = CASE WHEN Points IS NULL THEN 10 ELSE Points END, -- This bit works out whether it is a subsequent update FirstName = CASE WHEN Points IS NULL THEN T.FirstName ELSE I.FirstName END, Lastname = CASE WHEN Points IS NULL THEN T.Lastname ELSE I.Lastname END FROM demoInput I left outer join demoTarget T ON I.CID=T.CID WHERE T.CID IS NOT NULL -- Then insert any new stuff INSERT INTO demoTarget SELECT I.CID, I.Mobile, I.FirstName, I.Lastname, 'N' AS UpdateIdentifier, CAST(NULL as int) AS Points FROM demoInput I left outer join demoTarget T ON I.CID=T.CID WHERE T.CID IS NULLthen I believe you will get the behaviour you want.
First run with data as above:1 12345 Joe Bloggs N NULL 2 34567 Jane Bloggs N NULL
Second run with data ofTRUNCATE TABLE demoInput INSERT INTO demoInput (CID, Mobile, FirstName, LastName) values (1, '12345999','Joe','Bloggs'), (2, '34567999','Jane','Bloggs'), (3, '3435566', 'New','Person')1 12345999 Joe Bloggs U 0 2 34567999 Jane Bloggs U 0 3 3435566 New Person N NULLThen a third run of
TRUNCATE TABLE demoInput INSERT INTO demoInput (CID, Mobile, FirstName, LastName) values (1, '12345999','Joe2','Bloggs'), (2, '34567998','Jane','Bloggs2'), (3, '3435566', 'New','Person3')gives
1 12345999 Joe2 Bloggs U 10 2 34567998 Jane Bloggs2 U 10 3 3435566 New Person U 10
There may be some wrinkles to iron out, and your two target tables need to be sorted, but this should give you an idea of how to go about it
这篇关于查询根据条件更新列的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!