How to Insert/Upadte data based on exist in mathcin table, in single query

Question

Post reply

How to Insert/Upadte data based on exist in mathcin table, in single query

asita

SSCarpal Tunnel

Points: 4144
More actions
May 6, 2013 at 1:53 pm

#293598

Hello All,
please help me with this single select statement,
StudentNew table is exist with below values, (sno and sname is composite primary key)
Sno,
Sname,
LastGameDate,
DateofBirth,
Points
StudentOffers is the table exist with (sno is primary key)
Sno,
Sname,
DoB,
City,
Fee,
State
so in studentofferes is the warehouse table, it has 100K data and it get adds/updates every day,
now my question is, i have a new table StudentNew, it has some data in it (about 5k)
i would like to insert/update to the studentNew table based on SNO and SNAME columns match
If no Sno, Sname matching record in Student Name table,
then if Fee colum > 0
insert into studentNew Table (sno,sname,current week friday date,dob,fee)
else
insert into studentNew Table (sno,sname,null,dob,null)
if matching record found (sno, sname exist) in StudentNew table then
check LastGameDate column has value and +7 weeks to that value is > today then
if points column is null or 0 and Fee from studentOffers is > 0 then update points column with fee column, and update lastgamedate column to current week friday date
if points column value is already > 0 and points < Fee column from studentoffere then update points with fee column
check LastGameDate column has value and +7 weeks to that value is < today then no action
how to write this statement in more optimized manner please help me,
i do have step by step approach (in procedure., step by step checking)
please kindly help me, how to write in very efficient way
Kind Regards, thanks a ton in advance
dhanvi

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

asita SSCarpal Tunnel Points: 4144 More actions · Answer 1

Could Somebody please assist me here,

is it something possible?

any ideas

please,,,

Thanks in advace

dhani

Lowell SSC Guru Points: 323518 More actions · Answer 2

Asita, the question itself seems a lot like homework; Also, to get intelligent answers, you really need to provide DDL and sample data, to avoid a lot of assumptions, and to make everything pragmatically clear.

we would love to help you grasp the concepts, that's exactly why so many of us volunteer, but you've got to show us what you've got so far; show your work, and we'll offer pointers and concepts.

for the DDL,for example, i just guess all your columns are varchars, since you didn't say:

CREATE TABLE [dbo].[StudentNew] (

[Sno] VARCHAR(30) NOT NULL,

[Sname] VARCHAR(30) NOT NULL,

[LastGameDate] VARCHAR(30) NULL,

[DateofBirth] VARCHAR(30) NULL,

[Points] VARCHAR(30) NULL,

CONSTRAINT [PK_StudentNew] PRIMARY KEY CLUSTERED ([Sno],[Sname]) )

CREATE TABLE [dbo].[StudentOffers] (

[Sno] VARCHAR(30) NOT NULL,

[Sname] VARCHAR(30) NULL,

[DoB] VARCHAR(30) NULL,

[City] VARCHAR(30) NULL,

[Fee] VARCHAR(30) NULL,

[State] VARCHAR(30) NULL,

CONSTRAINT [PK_StudentOffers] PRIMARY KEY CLUSTERED ([Sno]) )

for the actual questions, you mentioned in BOLD "if matching record found (";

does that mean you are trying to learn how to use the MERGE statement?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Lowell SSC Guru Points: 323518 More actions · Answer 3

here is a very basic MERGE exampel that might help, and of course you should read up on it in your books and in SQL Books Online:

MERGE INTO dbo.tableA AS Target

USING(

SELECT ITEM, DESCRIPTION FROM dbo.tableB

) AS source

ON (target.ITEM = source.ITEM)

WHEN NOT MATCHED BY TARGET THEN

INSERT (ITEM, [DESCRIPTION])

VALUES (ITEM, DESCRIPTION);

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

asita SSCarpal Tunnel Points: 4144 More actions · Answer 4

pleae see the DDL and logic it require for this query

please assist me here,

create table Student

CREATE TABLE [dbo].[StudentNew] (

[Sno] VARCHAR(3) NOT NULL,

[Sname] VARCHAR(30) NOT NULL,

[LastGameDate] Date NULL,

[DateofBirth] Date NULL,

[Points] money NULL,

CONSTRAINT [PK_StudentNew] PRIMARY KEY CLUSTERED ([Sno],[Sname]) )

CREATE TABLE [dbo].[StudentOffers] (

[Sno] VARCHAR(3) NOT NULL,

[Sname] VARCHAR(30) NULL,

[DoB] Date NULL,

[City] VARCHAR(30) NULL,

[Fee] money NULL,

[State] VARCHAR(20) NULL,

)

Insert into StudentOffers values (1,'AAA','1/1/1987','NewYork',0,'NY')

Insert into StudentOffers values (2,'BBB','2/22/1986','Norwlak',5600,'CT')

Insert into StudentOffers values (3,'CCC','3/13/1989','Bridgeport',4400,'CT')

Insert into StudentOffers values (4,'DDD','4/24/1984','Stamford',6000,'CT')

Insert into StudentOffers values (5,'EEE','5/15/1987','Norwich',4000,'CT')

Insert into StudentOffers values (6,'FFF','6/5/1980','Secausus',3500,'NJ')

Insert into StudentOffers values (7,'GGG','7/22/1980','Newark',23000,'NJ')

Insert into StudentOffers values (8,'HHH','6/15/1990','Secausus',NULL,'NJ')

Insert into StudentOffers values (9,'III','7/17/1900','Newark',NULL,'NJ')

---Target Table All changes our Query Insert/Update will affect this table only

Insert into StudentNEW values (1,'AAA',null,'1/1/1987',null)

Insert into StudentNew values (2,'BBB',null,'2/22/1986',null)

Insert into StudentNew values (3,'CCC','4/19/2013','3/13/1989',100)

Insert into StudentNew values (4,'DDD','3/22/2013','4/24/1984',300)

Insert into StudentNew values (5,'EEE','2/23/2013','5/15/1987',250)

For Matched Records in StudentNew Table (based on sno,sname)

check LastGameDate column has no value

if Fee from studentOffers is > 0 then update points column with fee column, and update lastgamedate column to current week friday date

example: 1, has 0 value in fee so no action but sno=2 has value in fee so points = fee and then lastGamedate = current weekk friday

check LastGameDate column has value + 7 weeks > today then

if points column < Fee from studentOffers then update points column with fee column

example: sno with 3,4 has GameLastDate '4/19/2013', '3/22/2013' + 49 days (7 weeks) which is > today, check points < fee column so update points = fee

check LastGameDate column has value + 7 weeks < today then

No Action required.

example: sno with 5 has GameLastDate '2/23/2013' + 49 days (7 weeks) which is 2013-04-06 < today, so no action on sno=5

For NON Matched Records in StudentNew Table (based on sno,sname)

---Below Records Not exist in target Table, also null in fee Insert will happen with nulls in Lastgamedate, points columns

sno 8, 9 has no fee value (null) then insert total_summary_nfs studentnew table with null vlaues in lastdatecolumn & points

----Below Records not exist in target table,Fee > 0 so insert will happen with values

sno 6, 7 not exist in studentnew table also has fee value so insert with values LastGameDate = Current week friday

expected Results after the insert/update query runs in StudentNew Table

---Update

Insert into StudentNEW values (1,'AAA',null,'1/1/1987',null)

Insert into StudentNew values (2,'BBB','5/10/2013','2/22/1986',5600)

Insert into StudentNew values (3,'CCC','4/19/2013','3/13/1989',4400)

Insert into StudentNew values (4,'DDD','3/22/2013','4/24/1984',6000)

Insert into StudentNew values (5,'EEE','2/23/2013','5/15/1987',250)

---Insert

Insert into StudentOffers values (6,'FFF','5/10/2013','6/5/1980',3500)

Insert into StudentOffers values (7,'GGG','5/10/2013','7/22/1980',23000)

Insert into StudentOffers values (8,'HHH',null,'6/15/1990',NULL)

Insert into StudentOffers values (9,'III',null,'7/17/1900',NULL)

Thanks in advance

asittti

asita SSCarpal Tunnel Points: 4144 More actions · Answer 5

asita

SSCarpal Tunnel

Points: 4144

May 7, 2013 at 1:32 pm

#1613132

please help me

Kind Regards

asittti

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 6

asita (5/7/2013)
please help me
Kind Regards
asittti

for me at least...it would help to see what you currently have coded..as per your original comment below.

how to write this statement in more optimized manner please help me,
i do have step by step approach (in procedure., step by step checking)
please kindly help me, how to write in very efficient way

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

asita SSCarpal Tunnel Points: 4144 More actions · Answer 7

Thank you livingston,

i thought it is simple, but when i am writing the code indidvidual update stement it is taking time,

i am sorry it is not ready yet, but i am working on...

please forgive me for wrong wording....

Best Regards

asittti

asita SSCarpal Tunnel Points: 4144 More actions · Answer 8

Please Find the individual statements

--Update Statement

UPDATE a

set Points=

case when (a.lastGamedate is null and b.fee>0) then b.fee

when (a.lastGamedate is not null and dateadd(DAY,49,lastgamedate) >= getdate() and a.Points < b.Fee ) then b.Fee

end,

LastGameDate = case when (a.lastGamedate is null and b.fee>0) then DATEADD (D, -1 * DatePart (WEEKDAY , GETDATE()) + 7, getdate()) end

from StudentNew a inner join StudentOffers b on a.Sno =b.Sno and a.Sname =b.Sname

---Insert Statement

Insert into StudentNew

Select b.Sno ,b.Sname , case when (b.Fee>0) then DATEADD (D, -1 * DatePart (WEEKDAY , GETDATE()) + 7, getdate()) else null end ,b.DoB , case when ( b.Fee >0) then b.Fee else null end

from StudentNew a right join StudentOffers b on a.Sno =b.Sno and a.Sname =b.Sname

where a.Sno is null and a.Sname is null

this works but please assit me how can i make this into single statement as effective one

also if you observe somehow i am not feeling these statements the way i wrote is not good looking (even though it is functioning well) i mean for example rather than going the whole table read eveytime can i include where clause in update statement so that it is efficient

please kindly help me

thanks in advance

asita SSCarpal Tunnel Points: 4144 More actions · Answer 9

Could somebody please assist me

if i write update statement without any where clause, it is full table scan,

here the in my exist stable they have about 7 million rows, and also in new table (where update/insert take place) will have these many rows (after first insert/update),

rather than issueing stright update command, can we change the logic (functioanlly same) to include where clause etc. to restrict full table scan etc.,

or if you suggest it is good then i will go ahead

please advice me, also with merge statement

thank you

dhani

asita SSCarpal Tunnel Points: 4144 More actions · Answer 10

Finally merge staement worked as below

MERGE studentnew AS stTarg

USING (select [Sno], [Sname] , [DoB],[City],[Fee] , [State]from [StudentOffers]) AS studSrc

ON studSrc.sno=stTarg.sno and studSrc.sname = stTarg.sname

WHEN MATCHED THEN

UPDATE

set stTarg.Points=

case when (stTarg.lastGamedate is null and studSrc.fee>0) then studSrc.fee

when (stTarg.lastGamedate is not null and dateadd(DAY,49,stTarg.[LastGameDate]) >= getdate() and stTarg.Points < studsrc.Fee ) then studsrc.Fee

end,

stTarg.LastGameDate = case when (stTarg.lastGamedate is null and studsrc.fee>0) then DATEADD (D, -1 * DatePart (WEEKDAY , GETDATE()) + 7, getdate()) end

WHEN NOT MATCHED THEN

Insert ([Sno],[Sname], [LastGameDate] , [DateofBirth], [Points])

values (studSrc.Sno ,studSrc.Sname , case when (studSrc.Fee>0) then DATEADD (D, -1 * DatePart (WEEKDAY , GETDATE()) + 7, getdate()) else null end ,studSrc.DoB , case when ( studSrc.Fee >0) then studSrc.Fee else null end)

;

please help me with Update statement is there anyway to wriet case statment to more readability and efficientway of lkogic rather check the same condition again and again etc

please?