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.
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;;
The update statement looks fine. I have couple of comments:
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.
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)),'')
.....
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.