November 8, 2011 at 8:26 am
Having been excoriated elsewhere for having the nerve to ask this question, i'll try it here.
I have two tables: Civil.dbo.Subjects and Civil.dbo.receipts.
Subjects has a column called "Other" from which i need to move it's data to the "Comments" column in the Receipts table. However, i want to maintain the data already in the comments column of the receipts table. I've been told to use the following:
USE CIVIL
UPDATE Receipts
SET comments= other FROM civil.dbo.subjects + Comments FROM civil.dbo.RECEIPTS r
However, mgmt studio does not like the "+" in that line.. Also, i should add that this data needs to be matched up according to a common "Receiptno" value that is common between the two tables.
any help is appreciated.
thanks
james
November 8, 2011 at 8:38 am
-- run this first for an eyeball check
SELECT r.*, r.comments + ' ' + s.other
FROM civil.dbo.RECEIPTS r
INNER JOIN civil.dbo.subjects s
ON s.Receiptno = r.Receiptno
-- this is the update...from statement
UPDATE r
SET comments = comments + ' ' + s.other
FROM civil.dbo.RECEIPTS r
INNER JOIN civil.dbo.subjects s
ON s.Receiptno = r.Receiptno
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
November 8, 2011 at 8:41 am
Also, when you concatenate the data together, do you require any separators such as a Space or Period?
Here is a sample to get you started.....
USE CIVIL
GO
UPDATE r
SET comments = s.other + {insert desired separator if needed} + r.Comments
FROM civil.dbo.subjects s
JOIN civil.dbo.RECEIPTS r
ON r.ReceiptNo = s.ReceiptNo
Edit: I failed to read the part where you explained how the tables were linked.
November 8, 2011 at 8:45 am
Thanks. However, i'm getting the following with the Select statement:
Msg 402, Level 16, State 1, Line 1
The data types text and varchar are incompatible in the add operator.
Both the comments and the other columns are Text variables (deprecated in SQL 2008), so i might need to Cast something here? Correct? If so, how does that fit in?
November 8, 2011 at 8:49 am
Breakwaterpc (11/8/2011)
Thanks. However, i'm getting the following with the Select statement:Msg 402, Level 16, State 1, Line 1
The data types text and varchar are incompatible in the add operator.
Both the comments and the other columns are Text variables (deprecated in SQL 2008), so i might need to Cast something here? Correct? If so, how does that fit in?
It's a while since I've worked with text datatype but IIRC it's as easy as this:
-- run this first for an eyeball check
SELECT r.*,
NewValue = CAST(r.comments AS VARCHAR(MAX))+ ' ' + s.other
FROM civil.dbo.RECEIPTS r
INNER JOIN civil.dbo.subjects s
ON s.Receiptno = r.Receiptno
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
November 8, 2011 at 8:52 am
You got it. You'll need to convert to varchar(max) or nvarchar(max).
November 8, 2011 at 9:03 am
For some reason, i'm getting all twisted up and bumfuzzled here.
First off, where is this variable "NewValue" getting used once it's cast? Doesn't appear anywhere in the statement after that?
Sorry, but i must be missing something here.
November 8, 2011 at 9:08 am
Breakwaterpc (11/8/2011)
For some reason, i'm getting all twisted up and bumfuzzled here.First off, where is this variable "NewValue" getting used once it's cast? Doesn't appear anywhere in the statement after that?
Sorry, but i must be missing something here.
I believe Chris was just providing the "test" query to ensure you get the correct results. You will usually want to perform a select prior to the update statement just to ensure the join/where clauses provide the desired results.
UPDATE r
SET comments = convert(varchar(max), s.other) + ' ' + convert(varchar(max), r.comments)
FROM civil.dbo.RECEIPTS r
JOIN civil.dbo.subjects s
ON s.Receiptno = r.Receiptno
November 8, 2011 at 9:09 am
John Michael Robertson (11/8/2011)
Breakwaterpc (11/8/2011)
For some reason, i'm getting all twisted up and bumfuzzled here.First off, where is this variable "NewValue" getting used once it's cast? Doesn't appear anywhere in the statement after that?
Sorry, but i must be missing something here.
I believe Chris was just providing the "test" query to ensure you get the correct results. You will usually want to perform a select prior to the update statement just to ensure the join/where clauses provide the desired results.
UPDATE r
SET comments = convert(varchar(max), s.other) + ' ' + convert(varchar(max), r.comments)
FROM civil.dbo.RECEIPTS r
JOIN civil.dbo.subjects s
ON s.Receiptno = r.Receiptno
Spot on, John.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
November 8, 2011 at 9:17 am
That did the trick. Thank you all
November 8, 2011 at 9:28 am
However, one more issue with these tables. I know need to update part of a string that i placed in several different columns in the Subjects table. In a number of columns, i used case expressions to evaluate the length of the data proposed for that column, and, if the length exceeds the allowed size, the CE would place the string 'See Other' and then alternately place the intended data into the 'Other' column with appropriate prefixes in the string. Now, since i've moved that data from 'Other' in Subjects to 'Comments' in Receipts, i need to alter the string 'See Other' to now say 'See Comments'.
So, i assume i need to use and Update/Set/Replace statement to get this done. I'm sure i must first identify any and all of the columns to which i stuffed the string "See Other" into and then use the following:
USE Civil
UPDATE Subjects
SET Address= Replace (Address,'See Other','See Comments'), DBA= Replace (DBA,'See Other','See Comments'), and on and on and on with the other columns.....
Does this look right?
November 8, 2011 at 9:35 am
Breakwaterpc (11/8/2011)
However, one more issue with these tables. I know need to update part of a string that i placed in several different columns in the Subjects table. In a number of columns, i used case expressions to evaluate the length of the data proposed for that column, and, if the length exceeds the allowed size, the CE would place the string 'See Other' and then alternately place the intended data into the 'Other' column with appropriate prefixes in the string. Now, since i've moved that data from 'Other' in Subjects to 'Comments' in Receipts, i need to alter the string 'See Other' to now say 'See Comments'.So, i assume i need to use and Update/Set/Replace statement to get this done. I'm sure i must first identify any and all of the columns to which i stuffed the string "See Other" into and then use the following:
USE Civil
UPDATE Subjects
SET Address= Replace (Address,'See Other','See Comments'), DBA= Replace (DBA,'See Other','See Comments'), and on and on and on with the other columns.....
Does this look right?
Yes, i believe you are on the right track with using the REPLACE function.
November 8, 2011 at 9:37 am
Cool. Thanks.
i think i'll start using this site as opposed to "the other one".
thanks again.
November 8, 2011 at 10:01 am
John Michael Robertson (11/8/2011)
Breakwaterpc (11/8/2011)
However, one more issue with these tables. I know need to update part of a string that i placed in several different columns in the Subjects table. In a number of columns, i used case expressions to evaluate the length of the data proposed for that column, and, if the length exceeds the allowed size, the CE would place the string 'See Other' and then alternately place the intended data into the 'Other' column with appropriate prefixes in the string. Now, since i've moved that data from 'Other' in Subjects to 'Comments' in Receipts, i need to alter the string 'See Other' to now say 'See Comments'.So, i assume i need to use and Update/Set/Replace statement to get this done. I'm sure i must first identify any and all of the columns to which i stuffed the string "See Other" into and then use the following:
USE Civil
UPDATE Subjects
SET Address= Replace (Address,'See Other','See Comments'), DBA= Replace (DBA,'See Other','See Comments'), and on and on and on with the other columns.....
Does this look right?
Yes, i believe you are on the right track with using the REPLACE function.
After casting to a REPLACE-compatible datatype where necessary.
Something like SET Comments = REPLACE(CAST(Comments AS VARCHAR(MAX)),'old string','new string')
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply