January 28, 2011 at 8:34 am
I want to concatenate like so:
TableB.description = TableB.description + TableA.description
Here's the select to get TableA.description:
SELECT t3.description
FROM TableB AS t1 INNER JOIN
TableA AS t2 ON t1.SKU = t2.name INNER JOIN
TableA AS t3 ON t2.id = t3.parentid AND t3.type = 'feature' AND t2.showitem='yes'
Any suggestions would be greatly appreciated...
January 28, 2011 at 9:13 am
I don't understand your question.
Do you want to update TableB.description to include TableA ?
Sample data and results would help
January 28, 2011 at 9:26 am
I want to update TableB.description to append the TableA.description
The issue is there isn't a clean Foreign Key. I have to JOIN TableA back onto itself to get the description I want to append to TableB
I hope that clarifies the issue. I'll try to post some example data here in a follow-up post.
January 28, 2011 at 9:31 am
TABLE A
id =100
parentid =
sku = asdf1234
type = product
description = blah blah blah
showitem = yes
id =115
parentid = 100
sku =
type = feature
description = this is the data I want to append to TableB
showitem = yes
TABLE B
id = 225
sku = asd1234
description = this text should come first then append the text from TableA here
Hope that helps!
January 28, 2011 at 9:38 am
Looks like you have the logic to get TableA description, so can you switch it to an update with: SET B.DESCRIPTION = B.DESCRIPTION + ' ' + T3.DESCRIPTION
January 28, 2011 at 9:55 am
Right, but how do I do the WHERE clause of that UPDATE statement, so I'm updating the correct row in tableB?
January 28, 2011 at 10:36 am
If you only want to update certain rows, then use a regular WHERE clause at the end. Otherwise, leave it out to update all rows that satisfy the joins.
January 28, 2011 at 10:43 am
So is this right?
UPDATE TableB SET TableB.description = TableB.description + '(SELECT t3.description
FROM TableB AS t1 INNER JOIN
TableA AS t2 ON t1.SKU = t2.name INNER JOIN
TableA AS t3 ON t2.id = t3.parentid AND t3.type = 'feature' AND t2.showitem='yes')'
That doesn't look right to me.
January 28, 2011 at 11:03 am
I don't think that will work with the quote around your select. If you remove the quotes, it might work, but if the select returns nothing for some TableB records, I think it will update with NULL.
Maybe something like this ? Practice in a test table
UPDATE TableB
SET TableB.description = TableB.description + ' ' + t3.description
from TableB
INNER JOIN TableA AS t2 ON t1.SKU = t2.name
INNER JOIN TableA AS t3 ON t2.id = t3.parentid AND t3.type = 'feature' AND t2.showitem='yes'
(If you're lucky, one of the smart people here will respond.)
January 28, 2011 at 11:22 am
Thanks homebrew! I'm still verifying the data. At first glance, in test, it appears to have completed successfully.
Thanks for the guidance!
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply