Joining to large tables to perfrom update

I have 2 large tables that are over 11 million records each. I need to join
them on 1 field and then update 4 fields. So my script is this
update a
set a.field1= b.field1,
a.field2= b.field2,
a.field3 = b.field3,
a.field4 = bfield4
from a inner join b
on a.field5= b.field5
This query is taking a long time to run and I am wondering if there are any
join hints or lock hints that I can put in there to make it more efficient.
Any help is appreciated.an index on b(field5, field1, field2, field3, field4) might help with
this particular update.
Considering the performance of the whole system, it might or might not
be worth keeping, depending on your priorities.|||You can use
update a
set a.field1= b.field1,
a.field2= b.field2,
a.field3 = b.field3,
a.field4 = bfield4
from a inner join b with (nolock)
on a.field5= b.field5
however, for 11 million rows, it will still take a lot of time.
I would create script that executes the update in batches (Example: 1
million per batch based on field5). In other words, I would create a
"control" table where I can store the field5, the bacth number and when was
updated. This way even if any of the batch updates do not complete (for any
reason), you can start where you left off rather than start all over again.
"Andy" wrote:

> I have 2 large tables that are over 11 million records each. I need to jo
in
> them on 1 field and then update 4 fields. So my script is this
> update a
> set a.field1= b.field1,
> a.field2= b.field2,
> a.field3 = b.field3,
> a.field4 = bfield4
> from a inner join b
> on a.field5= b.field5
> This query is taking a long time to run and I am wondering if there are an
y
> join hints or lock hints that I can put in there to make it more efficient
.
> Any help is appreciated.

Joining multiple tables

I need your help on this. I am not getting the results i wanted in this script.

select distinct s.firstname, s.lastname, c[coursename],(midterm+final+assignment) as "total mark"

from student as s join studentrecord as sr

on (s.student#=sr.student#)

join course as c on (sr.course#=c.course#)

join courseenrollment as ce on (s.student#=ce.student#)

where ce.[status]=1

Now the results should only pick up 10 students who are enrolled (status=1) but the program is bypassing the where clause and returns all the results even though students were enrolled or not. Let me kknow your thoughts.

Join logic is applied in order, so if you rewrite your query like this it should provide the results expected.

1st, join the student and enrollment table on the student# using only the records with a status = 1 - this will return only students

2nd, join the student record table by the student# - this joins to the resultset of the 1st join

3rd, join the course table to the resultset of the previous joins by course# to the studentrecord table AND to the enrollment table.

Code Snippet

SELECT DISTINCT s.firstname, s.lastname, c.coursename,(midterm+final+assignment) as [total mark]

FROM student s INNER JOIN courseenrollment ce on ce.[student#] = s.[student#] AND ce.status = 1

INNER JOIN studentrecord sr on s.[student#] = sr.[student#]

INNER JOIN course c on c.[course#] = sr.[course#] AND c.[course#] = ce.course#]

Try this, and repost if it doesn't work or the assumption I made about the courseenrollment table having a course# field is incorrect.

|||

This is unexpected but logically true. If you add ce.status to the SELECT portion, I'm sure you'll see that the status = 1 for all records

Adamus

Joining multiple tables

I need your help on this. I am not getting the results i wanted in this script.

select distinct s.firstname, s.lastname, c[coursename],(midterm+final+assignment) as "total mark"

from student as s join studentrecord as sr

on (s.student#=sr.student#)

join course as c on (sr.course#=c.course#)

join courseenrollment as ce on (s.student#=ce.student#)

where ce.[status]=1

Now the results should only pick up 10 students who are enrolled (status=1) but the program is bypassing the where clause and returns all the results even though students were enrolled or not. Let me kknow your thoughts.

Join logic is applied in order, so if you rewrite your query like this it should provide the results expected.

1st, join the student and enrollment table on the student# using only the records with a status = 1 - this will return only students

2nd, join the student record table by the student# - this joins to the resultset of the 1st join

3rd, join the course table to the resultset of the previous joins by course# to the studentrecord table AND to the enrollment table.

Code Snippet

SELECT DISTINCT s.firstname, s.lastname, c.coursename,(midterm+final+assignment) as [total mark]

FROM student s INNER JOIN courseenrollment ce on ce.[student#] = s.[student#] AND ce.status = 1

INNER JOIN studentrecord sr on s.[student#] = sr.[student#]

INNER JOIN course c on c.[course#] = sr.[course#] AND c.[course#] = ce.course#]

Try this, and repost if it doesn't work or the assumption I made about the courseenrollment table having a course# field is incorrect.

|||

This is unexpected but logically true. If you add ce.status to the SELECT portion, I'm sure you'll see that the status = 1 for all records

Adamus

Joining multiple tables

Hi,
I need write a script get data from 3 different. All 3 tables will have
emailAddr, modify_date and sum details. But information may be duplicated in
the 3 tables, having same emailAddr but different modify_date eg. My end
result(new table) will contain the entry with latest modify_date if there is
a duplication of emailAddr, else the entry that is only available in single
table will also be insert into the new table.
Thanx..Can you give the primary key foriegn key trlationships with the table
definition and few sample data and expected result?
--
"yingying" wrote:

> Hi,
> I need write a script get data from 3 different. All 3 tables will have
> emailAddr, modify_date and sum details. But information may be duplicated
in
> the 3 tables, having same emailAddr but different modify_date eg. My end
> result(new table) will contain the entry with latest modify_date if there
is
> a duplication of emailAddr, else the entry that is only available in singl
e
> table will also be insert into the new table.
> Thanx..|||The tables are not related to each other.. only field that is similiar is th
e
emailAddr.. this is the field i expected to check..
Eg.. (table structure)
tblA (a_Id, emailAddr, dateModify, fname, lname)
tblB (b_Id, emailAddr, dateModify, addr, contact)
tblC (c_Id, emailAddr, dateModify, work1, work2)
tblResult (r_Id, emailAddr, dateModify)
(data in db)
tblA
1, aa@.abc.com, 29/04/2006, aa, aa
2, bb@.abc.com, 28/04/2006, bb, bb
tblB
1, aa@.abc.com, 30/04/2006, bb, bb
2, cc@.abc.com, 01/05/2006, cc, cc
3, bb@.abc.com, 03/05/2006, bb, bb
tblC
1, aa@.abc.com, 03/05/2006, aa, aa
2, dd@.abc.com, 01/05/2006, dd, dd
(Expected result stored into tblResult)
tblResult
1, aa@.abc.com, 03/05/2006
2, bb@.abc.com, 03/05/2006
3, cc@.abc.com, 01/05/2006
4, dd@.abc.com, 01/05/2006
"Omnibuzz" wrote:
> Can you give the primary key foriegn key trlationships with the table
> definition and few sample data and expected result?
> --
>
>
> "yingying" wrote:
>|||try this and let me know if this was what you required
select emailAddr,max(datemodify) as datemodify
from
(
select emailAddr,datemodify from tblA
union all
select emailAddr,datemodify from tblB
union all
select emailAddr,datemodify from tblC
) as A
group by emailaddr|||of course r_id in the tbl_result can be an identity
--
"yingying" wrote:

> Hi,
> I need write a script get data from 3 different. All 3 tables will have
> emailAddr, modify_date and sum details. But information may be duplicated
in
> the 3 tables, having same emailAddr but different modify_date eg. My end
> result(new table) will contain the entry with latest modify_date if there
is
> a duplication of emailAddr, else the entry that is only available in singl
e
> table will also be insert into the new table.
> Thanx..|||Thanx.. I got the idea how it works..
Another question.. If i was to have same email addr in the same table how am
i goin to get the data with the latest date having the same email addr.
(table structure)
tblA (a_Id, emailAddr, dateModify, lname, fname)
(data in table)
tblA
1, aa@.abc.com, 03/05/2006, aa, aa
2, aa@.abc.com, 01/05/2006, bb, bb
3, aa@.abc.com, 29/04/2006, cc, cc
4, dd@.abc.com, 01/05/2006, dd, dd
5, dd@.abc.com, 02/05/2006, ee, ee
6, ff@.abc.com, 01/05/2006, ff, ff
(Expected result)
1, aa@.abc.com, 03/05/2006, aa, aa
5, dd@.abc.com, 02/05/2006, ee, ee
6, ff@.abc.com, 01/05/2006, ff, ff
"Omnibuzz" wrote:

> try this and let me know if this was what you required
> select emailAddr,max(datemodify) as datemodify
> from
> (
> select emailAddr,datemodify from tblA
> union all
> select emailAddr,datemodify from tblB
> union all
> select emailAddr,datemodify from tblC
> ) as A
> group by emailaddr
>|||The same query will work for your requirement.
--
"yingying" wrote:
> Thanx.. I got the idea how it works..
> Another question.. If i was to have same email addr in the same table how
am
> i goin to get the data with the latest date having the same email addr.
> (table structure)
> tblA (a_Id, emailAddr, dateModify, lname, fname)
> (data in table)
> tblA
> 1, aa@.abc.com, 03/05/2006, aa, aa
> 2, aa@.abc.com, 01/05/2006, bb, bb
> 3, aa@.abc.com, 29/04/2006, cc, cc
> 4, dd@.abc.com, 01/05/2006, dd, dd
> 5, dd@.abc.com, 02/05/2006, ee, ee
> 6, ff@.abc.com, 01/05/2006, ff, ff
> (Expected result)
> 1, aa@.abc.com, 03/05/2006, aa, aa
> 5, dd@.abc.com, 02/05/2006, ee, ee
> 6, ff@.abc.com, 01/05/2006, ff, ff
>
> "Omnibuzz" wrote:
>|||I used same query but it did not return the right values i need..
select emailAddr, max(datestamp) as datemodify, fname, lname into #temp
from
(
select emailAddr, datestamp, fname, lname from tblA
) As A
group by emailAddr, fname, lname
(data in tblA)
1, aa@.abc.com, 29/04/2006, aa, aa
2, bb@.abc.com, 03/05/2006, bb, bb
3, cc @.abc.com, 03/05/2006, cc, cc
4, aa@.abc.com, 03/05/2006, aa2, aa2
(Expected result)
2, bb@.abc.com, 03/05/2006, bb, bb
3, cc @.abc.com, 03/05/2006, cc, cc
4, aa@.abc.com, 03/05/2006, aa2, aa2
but wat i got was
2, bb@.abc.com, 03/05/2006, bb, bb
3, cc @.abc.com, 03/05/2006, cc, cc
1, aa@.abc.com, 29/04/2006, aa, aa
4, aa@.abc.com, 03/05/2006, aa2, aa2
If cases with entry of same email addr, i will need to get the entry with
the latest date. When i didn't include the fname and lname, the result was
ok.. but after i add those 2 fields, the result was not wat i need.
Isit that i need to do this in 2 different steps in order to get the
required data'
Another question, when i have fname, lname in the 'select' query and not
having them in the 'group by', it gave me an error..
-->>
'A.fname' is invalid in the select list because it is not contained in
either an aggregate function or the GROUP BY clause.
what does this mean?
Thanx..
"Omnibuzz" wrote:
> The same query will work for your requirement.
> --
>
>
> "yingying" wrote:
>|||If you want the additional details with the latest date, try
select
emailAddr, datestamp as datemodify, fname, lname
into #temp
from tblA
where datestamp = (
select max(datestamp) from tblA as A2
where A2.emailAddr = tblA.emailAddr
)
nearly equivalent variations (differing in the case
where there are ties for the datestamp value, or
nullable columns) include
...
from tblA
where datestamp = (
select top 1 datestamp from tblA as A2
where A2.emailAddr = tblA.emailAddr
order by datestamp desc
)
and
...
from tblA
where not exists (
select * from tblA as A2
where A2.emailAddr = tblA.emailAddr
and A2.datestamp > tblA.datestamp
)
or in SQL Server 2005,
with Ranked(emailAddr, datestamp, fname, lname, rk) as (
select
emailAddr, datestamp, fname, lname,
rank() over (partition by emailAddr order by datestamp desc)
from tblA
)
select emailAddr, datestamp as datemodify, fname, lname
into #temp
from Ranked
where rk = 1
Steve Kass
Drew University
yingying wrote:
>I used same query but it did not return the right values i need..
>select emailAddr, max(datestamp) as datemodify, fname, lname into #temp
>from
>(
>select emailAddr, datestamp, fname, lname from tblA
> ) As A
>group by emailAddr, fname, lname
>(data in tblA)
>1, aa@.abc.com, 29/04/2006, aa, aa
>2, bb@.abc.com, 03/05/2006, bb, bb
>3, cc @.abc.com, 03/05/2006, cc, cc
>4, aa@.abc.com, 03/05/2006, aa2, aa2
>(Expected result)
>2, bb@.abc.com, 03/05/2006, bb, bb
>3, cc @.abc.com, 03/05/2006, cc, cc
>4, aa@.abc.com, 03/05/2006, aa2, aa2
>but wat i got was
>2, bb@.abc.com, 03/05/2006, bb, bb
>3, cc @.abc.com, 03/05/2006, cc, cc
>1, aa@.abc.com, 29/04/2006, aa, aa
>4, aa@.abc.com, 03/05/2006, aa2, aa2
>If cases with entry of same email addr, i will need to get the entry with
>the latest date. When i didn't include the fname and lname, the result was
>ok.. but after i add those 2 fields, the result was not wat i need.
>Isit that i need to do this in 2 different steps in order to get the
>required data'
>Another question, when i have fname, lname in the 'select' query and not
>having them in the 'group by', it gave me an error..
>-->>
>'A.fname' is invalid in the select list because it is not contained in
>either an aggregate function or the GROUP BY clause.
>what does this mean?
>Thanx..
>"Omnibuzz" wrote:
>
>|||If you are selecting more columns than what you had specified, then you will
have to use a correlated sub-query.
--
"yingying" wrote:
> I used same query but it did not return the right values i need..
> select emailAddr, max(datestamp) as datemodify, fname, lname into #temp
> from
> (
> select emailAddr, datestamp, fname, lname from tblA
> ) As A
> group by emailAddr, fname, lname
> (data in tblA)
> 1, aa@.abc.com, 29/04/2006, aa, aa
> 2, bb@.abc.com, 03/05/2006, bb, bb
> 3, cc @.abc.com, 03/05/2006, cc, cc
> 4, aa@.abc.com, 03/05/2006, aa2, aa2
> (Expected result)
> 2, bb@.abc.com, 03/05/2006, bb, bb
> 3, cc @.abc.com, 03/05/2006, cc, cc
> 4, aa@.abc.com, 03/05/2006, aa2, aa2
> but wat i got was
> 2, bb@.abc.com, 03/05/2006, bb, bb
> 3, cc @.abc.com, 03/05/2006, cc, cc
> 1, aa@.abc.com, 29/04/2006, aa, aa
> 4, aa@.abc.com, 03/05/2006, aa2, aa2
> If cases with entry of same email addr, i will need to get the entry with
> the latest date. When i didn't include the fname and lname, the result was
> ok.. but after i add those 2 fields, the result was not wat i need.
> Isit that i need to do this in 2 different steps in order to get the
> required data'
> Another question, when i have fname, lname in the 'select' query and not
> having them in the 'group by', it gave me an error..
> -->>
> 'A.fname' is invalid in the select list because it is not contained in
> either an aggregate function or the GROUP BY clause.
> what does this mean?
> Thanx..
> "Omnibuzz" wrote:
>