UNION or CONCANTENATE - ?
-
Using the code below, how would I change it so that I can retrieve data from 2 separate fields and dump into a third field? I know how to use the "union command" to retrieve the data, but I am not sure of how to incorporate it into an "update" script!! For example - how would I deposit "address 1, city, state, zip" fields into one field called "address?" I can obtain the result set I want, but can't get it to update the field I want. Thanks so much!
update EM
set EM.Address1 = Employees_Home_Address.StreetAddress,EM.[City] = Employees_Home_Address.[City],EM.[State] = Employees_Home_Address.[State],EM.ZIP = Employees_Home_Address.ZipCode
FROM
EM
INNER JOIN
Employees_Home_Address
ON
EM.Employee = Employees_Home_Address.Employee
-
do it the same way you would do a select statement and get the values into one column:
...
SET address = b.address1 + " " + b.city + ", " + b.state + " " + b.zip
...
You may need to convert/cast numerics to varchar.
If you want data from different *rows*, you need add them to the join and filter appropriately: col = a.xyz + c.abc
Also use ISNULL if any of the columns allow nulls. (string + null = null)
-
Using "+" in the formula causes me to get an error stating that "+" is for numbers, not text. That is the error I've been getting along when trying methods like the one you mentioned above. I also tried prefacing a field with text like this:
SET address = 'mailing address' + " " + b.address1
And received the same "for numbers, not text" error.
-
What is your exact statement and the error message you get?
And why do you want to denormalize your table?
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Try instead of " use ' this is proper syntax for doing this 'something' + ' ' + ..
Good Hunting!AJ Ahrens
webmaster@kritter.net -
Are all of the columns you are concatenating of type char or varchar? Numeric types will have to be converted (using CONVERT or CAST) to a character type.
For example, if Employees_Home_Address.StreetAddress and Employees_Home_Address.ZipCode are of type int, then you could something like this:
SET EM.Address1 = CONVERT(varchar(10), Employees_Home_Address.StreetAddress)
+ Employees_Home_Address.[City]
+ Employees_Home_Address.[State]
+ CONVERT(varchar(10), Employees_Home_Address.ZipCode)
-
Not sure of your exact needs here, but you may be better off creating a view having a column that is the concatenation of all the needed fields. Otherwise, you risk (during denormalizing) having the field be out of sync with the root fields when data changes.
My guess the "+" does not work because you have a numeric field (zip?) in there.
- Manash
-
Ok. Here is what I actually want to do. I want ContactCustomTabFields.TextTransfer AND ContactCustomTabFields.Previous_Employment to be inserted into the empty/null field: Contacts.Memo
All of the fields concerned are of type text. I would like "Text Transfer" to come first, followed by a space, followed by "Previous Employment."
The below script results in this message:
Server: Msg 403, Level 16, State 1, Line 1
Invalid operator for data type. Operator equals add, type equals text.
Update Contacts
Set memo = TextTransfer + Previous_Employment
From ContactCustomTabFields
Where Contacts.ContactID = ContactCustomTabFields.ContactID
and Previous_Employment is not null
Thanks for your help!
-
Ok,
You cannot do that with ntext, text, image..etc fields. Instead, you need to grab a portion of the text field using the substring function. Use a length that you are comfortable will capture all characters. Or, change the type to varchar/nvarchar for the source fields.
-
If the text columns contain text less than 8000 characters in length, you could convert to varchar, like this:
UPDATE Contacts
SET memo = CONVERT(varchar(8000), cct.TextTransfer) + ' ' + CONVERT(varchar(8000), cct.Previous_Employment)
FROM Contacts c JOIN ContactCustomTabFields cct ON c.ContactID = cct.ContactID
WHERE Previous_Employment IS NOT NULL
Otherwise, you'll have to use the TEXTPTR and UPDATETEXT statements in a loop, and do the update in chunks.
-
Can't a combination of "union all" and "insert" be used to accomplish this?
-
This code:
UPDATE
Contacts
SET
Memo = 'Previous Employment: ' + ContactCustomTabFields.Previous_Employment
FROM
Contacts
INNER JOIN
ContactCustomTabFields
ON
Contacts.ContactID = ContactCustomTabFields.ContactID
WHERE
ContactCustomTabFields.Previous_Employment is not null
Results in this error (despite both fields being type "text")
Server: Msg 403, Level 16, State 1, Line 1
Invalid operator for data type. Operator equals add, type equals text.
-
I tried suggestion above with this code:
UPDATE Contacts
SET memo = 'Previous Employment: ' + CONVERT(varchar(8000), Previous_Employment)
FROM Contacts JOIN ContactCustomTabFields ON Contacts.ContactID = ContactCustomTabFields.ContactID
WHERE Previous_Employment IS NOT NULL
and received the same error message (despite all fields concerned being of type text)
Server: Msg 403, Level 16, State 1, Line 1
Invalid operator for data type. Operator equals add, type equals text.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply