How can I do a bulk update from Table1 to Table2?
-
I have the following scenario: Table1 has 3 columns (ID,Name,description); and Table2 also has 3 columns (ID,Name,description). I need to update the 'description' field on Table 2 (old data) with the 'description' on Table1 (new data). All records from Table2 exist in Table1
I tried this but does not work.
UPDATE Table2 SET
[Table2].[dbo].[description] = (select [Table1].[dbo].[description])
WHERE
[Table2].[dbo].[id] = [Table1].[dbo].[id]
/
My little brain cannot compute this far... -
- update t2
- set description = t1.description
- from dbo.Table1 t1
- join dbo.Table2 t2
- on t1.id = t2.id
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant -
Tim, here is the exact SQL I am using and the error returned:
update CAT_products
set DescriptionHTML = t1.[Long_Description_Code]
from dbo.Product_Detail t1
join dbo.CAT_products t2
on t1.modelnumber = t2.productNumber
Server: Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column 'DescriptionHTML', table 'dnn3toolservedb.dbo.CAT_Products'; column does not allow nulls. UPDATE fails.
The statement has been terminated.
P.S. Do you have a relative here in Tampa Florida? My accountant's name is John Wilkinson... Just curios...
-
No, it's a fairly common English name and a brand of razor in Europe.
Your problem is what the error msg says. You have a null in product_details, but descriptionHTML in CAT_products won't allow NULLs.
you can use:
- update CAT_products
set DescriptionHTML = t1.[Long_Description_Code]
from dbo.Product_Detail t1
join dbo.CAT_products t2
on t1.modelnumber = t2.productNumber
- where t1.[Long_Description_Code] is not null
or if you need to overwrite all the matched records in CAT_products, you can either alter the column to allow nulls (but check for knock-on effects), or use:
update CAT_products
set DescriptionHTML = isnull(t1.[Long_Description_Code],'')
from dbo.Product_Detail t1
join dbo.CAT_products t2
on t1.modelnumber = t2.productNumber
which will set the value to a zero-length string instead of NULL.
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant - update CAT_products
-
Mr. razor, you are sharp....
Thank you very much for all your help!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply