Query Problems!

I'm having a problem write a query that pulls the new updated data from tbl1. The fields are state_no and date_insp. the criteria for the search is 00054000 & 1/1/2003 until present time.
I want to compare that data to tbl2 to see if it has any pre-existing data such as tbl1.Owner name, tbl1.Owner_addy, tbl2Owner_name, tbl2.Owner_addy so that I'm not duplicating the date in tbl2 when i do the import.

If this isn't clear to your please write back and I will explain more.HERE'S A QUERY THAT i CAME UP WITH TO TRY AND SEE IF IT WORKS BUT I DON'T THINK THAT ITS CHECKING EARLIER STATE_NO:

SELECT DISTINCT STATE_NO, OWNER_NAME, BUSINESS_NAME
FROM TESTNEWIMPORTBOILER T, HBC_CONTACT C
where state_no >= '00054000' and date_inspection >= '1/1/2003'
AND
T.OWNER_NAME =
(SELECT BUSINESS_NAME
FROM HBC_CONTACT
WHERE BUSINESS_NAME = T.OWNER_NAME)

Originally posted by Bigced_21
I'm having a problem write a query that pulls the new updated data from tbl1. The fields are state_no and date_insp. the criteria for the search is 00054000 & 1/1/2003 until present time.
I want to compare that data to tbl2 to see if it has any pre-existing data such as tbl1.Owner name, tbl1.Owner_addy, tbl2Owner_name, tbl2.Owner_addy so that I'm not duplicating the date in tbl2 when i do the import.

If this isn't clear to your please write back and I will explain more.

Query Problem

Hi to All!
we have recently updated our reference codes from 3 digits to 7 digits
due to shortage of reference codes
first we have reference no like
reference no
101
102
103
.
.
.
now we have changed the reference nos to 7 digits due to some
constraints like
1001101
1001201
3001101
1001102
2001102
.
.
.
i have table in which both old and aginst them new no is stored like
Old_Code New_Code
101 1001101
201 1001201
103 1001103
in production table reference no comes like
reference no
101-ABC-06
201-DEF-06
.
.
.
in production table i have more than 1 million records. now
want to update old reference no with new one like
Old reference no New_reference_no
101-ABC-06 1001103-ABC-06
201-DEF-06 1001101-DEF-06
.
.
.
how would i b able to update new reference codes in production table
What will be the query to update the old record
Thanx
Farid
*** Sent via Developersdex http://www.examnotes.net ***Can you give the create table scripts.
--
"Jami" wrote:

> Hi to All!
> we have recently updated our reference codes from 3 digits to 7 digits
> due to shortage of reference codes
> first we have reference no like
> reference no
> 101
> 102
> 103
> ..
> ..
> ..
> now we have changed the reference nos to 7 digits due to some
> constraints like
> 1001101
> 1001201
> 3001101
> 1001102
> 2001102
> ..
> ..
> ..
> i have table in which both old and aginst them new no is stored like
> Old_Code New_Code
> 101 1001101
> 201 1001201
> 103 1001103
> in production table reference no comes like
> reference no
> 101-ABC-06
> 201-DEF-06
> ..
> ..
> ..
> in production table i have more than 1 million records. now
> want to update old reference no with new one like
> Old reference no New_reference_no
> 101-ABC-06 1001103-ABC-06
> 201-DEF-06 1001101-DEF-06
> ..
> ..
> ..
> how would i b able to update new reference codes in production table
> What will be the query to update the old record
> Thanx
> Farid
>
> *** Sent via Developersdex http://www.examnotes.net ***
>|||
Create Table Reference_Codes (Old_code char(3), New_Code char(7))
go
Create table main_table(Main_Id Char(5), Reference_code char(14),name
varchar(55)... )
here first referece code is of 10 characters with following format
101-ABC-06
now it is
1001101-ABC-06
*** Sent via Developersdex http://www.examnotes.net ***|||Try this.. Hope this helps.
update A
set Reference_code = left(new_code,4) + Reference_code
from main_table A, Reference_Codes B
where left(A.reference_code,3) = B.Old_Codesql