SQL 2008 - Inner join same table field with left character count 1

[ITALIAORIANA]

Hi,

I have a table that I need to update blank description fields with an existing description. The existing description is based on a 5 character code. (example 12345 is the code) I need to update any other codes with the same initial 5 characters with that description. So 12345AB, 12345CD, 12345EF should all have the same description as 12345 for any that are blank. I wrote a select query to review the results, however it is not working properly because I keep getting zero results. Can someone tell me if this is written incorrectly for what I am looking to do?

SELECT A.IPCD_ID, A.IPCD_DESC, B.IPCD_DESC FROM CMC_IPCD_PROC_CD A
INNER JOIN CMC_IPCD_PROC_CD B ON A.IPCD_ID = LEFT(B.IPCD_ID,5)
WHERE A.IPCD_DESC = ''


Thanks
Deana

 

[imex]

Hi,

Can you post a small sample of data from the table and its expected result?

[URL unfurl="true"]http://www.imoveisemexposicao.com.br/imobiliarias-em-guarulhos[/url]

 

[ITALIAORIANA]

It would be similar to this: I need to update the IPCD description for the right with what is showing in the left but I can't even get the select query to work properly.


IPCD_ID IPCD_DESC
12345 Transcutaneous electrical joint stimulation
54321 Trapeze bar, heavy duty, for patient weight
E1235 Portable oxygen concentrator, rental
F1235 Implantable neurostimulator radiofrequency receiver
S2222 Radiofrequency transmitter (external)


IPCD_ID IPCD_DESC
12345KJ Transcutaneous electrical joint stimulation
12345AB Transcutaneous electrical joint stimulation
12345JJ Transcutaneous electrical joint stimulation
54321AB Trapeze bar, heavy duty, for patient weight
E1235SS Portable oxygen concentrator, rental
F1235DG Implantable neurostimulator radiofrequency receiver
S2222SS Radiofrequency transmitter (external)

 

[ITALIAORIANA]

Ok, scratch that previous one. The actual results NOW should look like this: The top description is what I am looking to update the below blank description with.

IPCD_ID IPCD_DESC
12345 Transcutaneous electrical joint stimulation
54321 Trapeze bar, heavy duty, for patient weight
E1235 Portable oxygen concentrator, rental
F1235 Implantable neurostimulator radiofrequency receiver
S2222 Radiofrequency transmitter (external)


IPCD_ID IPCD_DESC
12345KJ
12345AB
12345JJ
54321AB
E1235SS
F1235DG
S2222SS

 

[imex]

Try:

INNER JOIN CMC_IPCD_PROC_CD B ON B.IPCD_ID = LEFT(A.IPCD_ID,5)

Hope this helps.

[URL unfurl="true"]http://www.imoveisemexposicao.com.br/imobiliarias-em-guarulhos[/url]

 

[ITALIAORIANA]

Thanks imex, that worked perfectly. I am trying to do the actual update now and having difficulty.

UPDATE CMC_IPCD_PROC_CD
SET IPCD_DESC = (SELECT A.IPCD_ID, A.IPCD_DESC,B.IPCD_ID, B.IPCD_DESC
FROM CMC_IPCD_PROC_CD A INNER JOIN CMC_IPCD_PROC_CD B ON B.IPCD_ID = LEFT(A.IPCD_ID,5)
WHERE A.IPCD_DESC ='')


I keep getting the following error.

Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

Thanks
Deana

 

[imex]

Try:

UPDATE A
SET A.IPCD_DESC = B.IPCD_DESC
FROM CMC_IPCD_PROC_CD A 
INNER JOIN CMC_IPCD_PROC_CD B ON B.IPCD_ID = LEFT(A.IPCD_ID,5) 
WHERE A.IPCD_DESC = ''

Hope this helps.

[URL unfurl="true"]http://www.imoveisemexposicao.com.br/imobiliarias-em-guarulhos[/url]

 

[ITALIAORIANA]

Thanks so much!!! I was making it more complicated than needed. I have so many databases to review and update, this is a tremendous help!!!!

Deana