Tricky one

[r0nke]

I know nothing about sql programming but this has been dropped into my plate:

Table 1:

serial_number model_number
123456
225588
663399
447788

Table 2:

Model_number beginning_stringOF_serial_number
E3333 447
C3333 225
rtf111 123
e3 663

Table 1 (model_number) is currently blank.
I need to use the logic in table 2 to populate the model number in table 1.
For example, (in table 2) every serial number that starts with '447' would have model_number = 'e3333'. So I need to find every instance of serial number in table 1 which starts with '447' and populate the resulting table1.model_number with 'e3333'.

This I dont know how to handle.

Could someone help please.

Thanks

 

[bdreed35]

I believe this is what you need:

UPDATE Table_1
SET t1.model_number = t2.model_number
FROM Table_1 t1, Table_2 t2
WHERE LEFT(t1.serial_number, 3) = t2.beginning_stringOF_serial_number


~Brian

 

[maswien]

try this:

update table1 set model_number = (select max(Model_number) from table2
where beginning_stringOF_serial_number = substring(table1.serial_number, 1, 3))

 

[r0nke]

Hi

I tried it and it worked perfectly, the only thing is I need to save this in a package to run once a day. I ran the package and it keeps timing out.

Is there a way round this?

The table has a potential to grow up to 10million rows.

Thanks

 

[maswien]

which is table has potential to grow to 10 millions, table1 or table2?

Why you need two tables here? Can you tell me more info about what you want to do?

 

[r0nke]

Thanks Mija

Table one is the bigger one, table 2 is where the logic resides, that would never get any bigger.

Does this make sense?

Thanks

 

[autocutter]

"I tried it and it worked perfectly, the only thing is I need to save this in a package to run once a day. I ran the package and it keeps timing out"

You need to find out from the DBA whether it is an "out of resources" error or query time out setting that is stopping a complete run-most likely a time out.

Additionally, make sure that table one has an index for serial_number and explore a cover index for the table two's fields Model_number & beginning_stringOF_serial_number


To run daily, make it a Job under Management/SQL Agent/Jobs