SQLTeam.com | Weblogs | Forums

MS SQL Database - Combining 2 Columns


#1

I have Table A which has columns Product_id & Product_Price. Table B has Product_id & Product_desc.

Product_id is type char(30). product_Price is smallmoney and Product_desc is nvarchar (2000).

I am trying to produce a query which for each value of Product_id, we take Product_desc add TEXT followed by product_price x 4.

So I am thinking that I start with Update Table B SET Product_desc = Prouct_desc + TEXT but when I run this sql reports Conversion failed when converting the varchar value '180110"" ' to data type int.

My second problem is the correct syntax to append product_price.

I would be very grateful for some help with this please.


#2

If you just want to create a query that returns an output, don't update the product_desc column. If you do, then if you just query only for product_desc column, you are going to get the new string which includes product_price.

When you try

You can write a query to get what you want - like this:

    select
	a.Product_Id,
	a.Product_Price,
	b.Product_Desc,
	b.Product_Desc + ' TEXT ' + cast(a.Product_Price * 4 as varchar(32))
		as CombinedCol
from
	TableA a
	inner join TableB b on
		a.Product_Id = b.Product_id;;

#3

Thanks JamesK,

That makes sense. But I do want to make a permanent change o the product_desc column.

So I guess:

UPDATE B
SET Product_desc = b.Product_desc + 'TEXT' + cast(a.product_price * 4 as varchar(255))'
FROM B
JOIN A ON B.product_d = A.product_id;


#4

The update statement looks fine. I have couple of comments:

  1. This way of doing the updates is not ANSI compliant (which in and of itself is not an issue unless you are a purist), but make sure that you alias the tables, and also make sure that the joins are on one-to-one relations (unless you want one-to-many updates). In your case since you are joining on PK, this is not an issue.

  2. If there are null values in either the product_desc column or product_price column, the update would cause the product_desc to be set to null. If that is not the intention, then change the set portion of the update to this:

    .....
    SET Product_desc =
    COALESCE(b.Product_desc,'')
    + 'TEXT'
    + COALESCE(CAST(a.product_price * 4 AS VARCHAR(255)),'')
    .....


#5

Updating the main product_desc, particularly with data from a different table, is still a terrible idea. You should just create a view that materializes (generates) that custom description with price when you need it.