0 Replies - 1474 Views - Last Post: 11 February 2014 - 11:43 AM

#1 dnguyen491  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 1
  • Joined: 10-February 14

Updating a SQL Table Column Using Values From a Different SQL Table

Posted 11 February 2014 - 11:43 AM

Hello all,

I need to update a column in a SQL table using values from a DIFFERENT table based on multiple conditions. I have an excel sheet that I've already imported into a SQL table called MISSING_TACS. This table contains a TAC number, Manufacturer, and a Model Number. I need to import the TAC numbers into an existing table called tblModel_copy based on several conditions as well.

The code below pulls up a table as seen in the attachment pic1.png. In this image, you will see 3 columns to the right of the red line (this is all coming from the MISSING_TACS. Everything from the left of the red line is pulling from several different tables that are linked together. What I have boxed in yellow is basically six records I've pulled up that have matching model numbers and manufacturers/vendors. Since those are matching, I need to import all values from the TAC column into the IMEI column. For example, the first record should have a comma delimited value of 01036600,01048100,01050400 and so would the next 2 records. Then record #4 should have a comma delimited value of 35251500,35379100,35433500.

SELECT R.Request_ID, RT.Request_Type, M.Model_ID, M.Model_Num, M.IMEI, V.Vendor_Name, MT.*
FROM tblRequest AS R, tblModel_copy AS M, tblVendor AS V, tblRequestCertType AS RCT, tblRequestType AS RT, tblStatus AS S, Missing_TACs as MT
WHERE R.Model_ID = M.Model_ID
	AND M.Vendor_ID = V.Vendor_ID
	AND R.Request_ID = RCT.Request_ID
	AND R.Request_Type_ID = RT.Request_Type_ID
	AND S.Status_ID = R.Status_ID
	AND M.Model_Num = MT.Model_Num
	AND V.Vendor_Name = MT.Manufacturer
	AND RCT.Cert_Type_ID IN (2, 14) 
	AND R.Request_Type_ID IN (1, 2, 3) 
	AND NOT (M.IMEI IS NULL) 
	AND R.Status_ID NOT IN (7, 8)
	AND ',' + M.IMEI + ',' not like '%,MT.TAC,%'



Essentially, I am importing these values into tblModel_copy since that is where the IMEI column is coming from; I need to update this depending on those same conditions above and essentially where the model_num values match and the vendor_name/manufacturer values match. It will definitely be an UPDATE statement with some sort of concatenation since some records have existing values in the IMEI column. Here is what I have so far:

UPDATE tblModel_copy
SET IMEI = IMEI + ',' + MT.TAC
FROM tblRequest AS R, tblModel_copy AS M, tblVendor AS V, tblRequestCertType AS RCT, tblRequestType AS RT, tblStatus AS S, Missing_TACs as MT
WHERE R.Model_ID = M.Model_ID
	AND M.Vendor_ID = V.Vendor_ID
	AND R.Request_ID = RCT.Request_ID
	AND R.Request_Type_ID = RT.Request_Type_ID
	AND S.Status_ID = R.Status_ID
	AND M.Model_Num = MT.Model_Num
	AND V.Vendor_Name = MT.Manufacturer
	AND RCT.Cert_Type_ID IN (2, 14) 
	AND R.Request_Type_ID IN (1, 2, 3) 
	AND NOT (M.IMEI IS NULL) 
	AND R.Status_ID NOT IN (7, 8)



However, when I run this code, it will update but only the first matching record it runs into as you can see pic2.png. Record 1-3 received 01036600, but skipped 01048100 and 01050400. How do I loop through these TAC numbers to make sure the IMEI column receives all the TAC values?

(If this is relevant or if anyone is curious, I will be using a Coldfusion page to run this code. However, I primarily need to know how to do this in SQL.)

Thanks in advance! Hopefully, I've explained this well enough.

Attached image(s)

  • Attached Image
  • Attached Image


Is This A Good Question/Topic? 0
  • +

Page 1 of 1