Query to update a column based on a condition

Question

0.00/5 (No votes)

See more:

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.

Posted 1-Jun-17 7:16am

chints786

Updated 1-Jun-17 12:15pm

Maciej Los

v2

Add a Solution

Comments

ZurdoDev 1-Jun-17 13:54pm

Sounds good. What is your question?

chints786 1-Jun-17 14:01pm

can you help me with the code to implement?

ZurdoDev 1-Jun-17 14:03pm

Sure. Where are you stuck?

chints786 1-Jun-17 14:07pm

I have included the flags in table a and table b . the confusion is say for eg if initially the record came in first file is Ryan and the updateidentifier is N, then both the tables will have that record.. but in the second file if the name comes as ryandev and update identifier U i need to update the same record and increement the flag variable to 1...how can i achieve that

ZurdoDev 1-Jun-17 14:13pm

I don't really follow but essentially all you're doing, it sounds like is:
UPDATE table
SET Name = 'ryandev', flag = 1
WHERE CID = 1

Maciej Los 1-Jun-17 14:13pm

You mean, you want T-SQL only or something else (C#, VB.NET)?

chints786 1-Jun-17 14:18pm

yes Tsql only

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Answer 1 · 2017-06-01T12:15:00

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...

SQL

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 run

SQL

INSERT 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 NULL

then 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 of

SQL

TRUNCATE 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	NULL

Then a third run of

SQL

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