Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

How to Insert/Upadte data based on exist in mathcin table, in single query Expand / Collapse
Author
Message
Posted Monday, May 6, 2013 1:53 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, May 29, 2014 4:55 PM
Points: 139, Visits: 279
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
Post #1449865
Posted Monday, May 6, 2013 2:24 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, May 29, 2014 4:55 PM
Points: 139, Visits: 279
Could Somebody please assist me here,

is it something possible?

any ideas


please,,,

Thanks in advace
dhani
Post #1449879
Posted Monday, May 6, 2013 2:34 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 5:58 AM
Points: 12,877, Visits: 31,791
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1449882
Posted Monday, May 6, 2013 2:37 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 5:58 AM
Points: 12,877, Visits: 31,791
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1449884
Posted Tuesday, May 7, 2013 12:31 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, May 29, 2014 4:55 PM
Points: 139, Visits: 279
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
Post #1450294
Posted Tuesday, May 7, 2013 1:32 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, May 29, 2014 4:55 PM
Points: 139, Visits: 279
please help me

Kind Regards
asittti
Post #1450314
Posted Tuesday, May 7, 2013 1:42 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 9:52 AM
Points: 1,876, Visits: 18,454
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 !
__________________________________________________________________
Post #1450315
Posted Tuesday, May 7, 2013 1:57 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, May 29, 2014 4:55 PM
Points: 139, Visits: 279
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
Post #1450321
Posted Tuesday, May 7, 2013 3:15 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, May 29, 2014 4:55 PM
Points: 139, Visits: 279
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
Post #1450341
Posted Tuesday, May 7, 2013 4:41 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, May 29, 2014 4:55 PM
Points: 139, Visits: 279
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
Post #1450360
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse