UPDATE AN EXISTING TABLE - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

UPDATE AN EXISTING TABLE

Author

Topic

divan
Posting Yak Master

153 Posts

Posted - 2012-08-13 : 08:15:33

I HAVE AN EXISTING TABLE CALLED CONTACT_INFO WITH THE FOLLOWING FIELDS
CONTACT_NUM, CONTACT_NAME, CONTACT_SSN. THE ONLY FIELD POPULATED CURRENTLY IS THE CONTACT_NUM. I HAVE ANOTHER TABLE CLIENT THAT HAS THE FOLLOWING FIELD CLIENT_NUM, CLIENT_SSN, CLIENT_NAME. THE CONTACT NUM AND THE CLIENT NUM ARE THE SAME (KEY)..

I NEED TO READ THE CONTACT FILE USING THE CONTACT_NUM AND FIND THE CLIENT_NUM AND UPDATE CONTACT_INFO FIELDS CONTAC_NAME, CONTACT_SSN WITH INFO IN THE CLIENT TABLE...

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-13 : 08:41:16

You can run the following query to do the update:

UPDATE ci SET
	ci.CONTACT_NAME=cl.CLIENT_NAME,
	ci.CONTACT_SSN=cl.CLIENT_SSN
FROM
	CONTACT_INFO ci
	INNER JOIN CLIENT cl ON cl.CLIENT_NUM = ci.CONTACT_NUM;

Before you do the update though, run this to see what is going to be updated. The first 3 columns show the current data in the CLIENT_INFO table and the next 3 the data in CLIENT. The update will copy columns 5 and 6 to columns 2 and 3.

SELECT
	ci.CONTACT_NUM,
	ci.CONTACT_NAME,
	ci.CONTACT_SSN,
	cl.CLIENT_NUM,
	cl.CLIENT_NAME,
	cl.CLIENT_SSN
FROM
	CONTACT_INFO ci
	INNER JOIN CLIENT cl ON cl.CLIENT_NUM = ci.CONTACT_NUM;

divan
Posting Yak Master

153 Posts

Posted - 2012-08-13 : 08:45:51

Sunitabeck when I run the script I get the following message

Server: Msg 8152, Level 16, State 14, Line 1
String or binary data would be truncated.
The statement has been terminated.

divan
Posting Yak Master

153 Posts

Posted - 2012-08-13 : 08:59:41

I FIGURED OUT THAT IT THE NAME FIELD THAT IS CAUSING THE ISSUE.. IS THERE ANY WAY TO TELL THE SCRIPT TO IGNORE THE ISSUE?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-13 : 09:06:55

Likely cause is that the column width for contact_name and/or contact_ssn is smaller than the corresponding columns in the client table. Run these two queries to see what the column widths in each table are:

SELECT
	table_name,
	column_name,
	character_maximum_length
FROM
	Information_schema.columns
WHERE
	table_name = 'CONTACT_INFO'
	AND column_name IN ('CONTACT_NAME', 'CONTACT_SSN');
	
SELECT
	table_name,
	column_name,
	character_maximum_length
FROM
	Information_schema.columns
WHERE
	table_name = 'CLIENT'
	AND column_name IN ('CLIENT_NAME', 'CLIENT_SSN');

If the values from the first query are smaller than the values from the second, run this query to confirm - this tells you the maximum length of the data in the client table that is to be copied to the contact info table.

SELECT
	MAX(LEN(cl.CLIENT_NAME)) AS MaxClientNameLength,
	MAX(LEN(cl.CLIENT_SSN)) AS MaxClientSSNLength
FROM
	CONTACT_INFO ci
	INNER JOIN CLIENT cl ON cl.CLIENT_NUM = ci.CONTACT_NUM;

If that indeed is the case, you may want to consider expanding the size of the columns in your target table - but if this is a production system, you have to see how else that table is being used and whether changing the column width would have any impact.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-13 : 09:09:21

quote:
Originally posted by divan

I FIGURED OUT THAT IT THE NAME FIELD THAT IS CAUSING THE ISSUE.. IS THERE ANY WAY TO TELL THE SCRIPT TO IGNORE THE ISSUE?

You can tell it to store only as much as it can store by doing something like this, assuming the contact_info tables have client_name as char/varchar(255) and ssn as char/varchar(11)

UPDATE ci SET
	ci.CONTACT_NAME=LEFT(cl.CLIENT_NAME,255),
	ci.CONTACT_SSN=LEFT(cl.CLIENT_SSN,11)
FROM
	CONTACT_INFO ci
	INNER JOIN CLIENT cl ON cl.CLIENT_NUM = ci.CONTACT_NUM;

But, you will lose some of the data if you do that.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources