Updating a date with a correlated subquery

[rlkemp]

I need to take a month and day field in a table, and update a new date field, based upon the month and year. I tried this query, which says it updates the records, but none of the date fields are updated. Can someone tell me what is wrong with the query or suggest a better one? Thanks.
UPDATE dbo.pd_products
SET launch_date =
(select cast( B.launch_year + '-' + B.launch_month + '-' + '1' AS datetime)
from dbo.pd_products as B
where B.product_key = product_key
and B.launch_date = launch_date)
where substring(launch_year,1,1) != ' '
and substring(launch_month,1,1) != ' '

 

[manjarikosaraju]

Try this.. I haven't checked the results of the query..
I am not sure what is the reason for the where clause in your query. If it is to check whether the datefield is null, you need not have it, I suppose. Let me know if it doesn't work.


UPDATE dbo.pd_products SET launch_date = select cast( B.launch_year + '-' + B.launch_month + '-' + '1' AS datetime)
from dbo.pd_products A join dbo.pd_products B
on A.product_key = B.product_key
and A.launch_date = B.launch_date
where substring(A.launch_year,1,1) != ' '
and substring(A.launch_month,1,1) != ' '


Hope this helps.

 

[rlkemp]

I tried the query in SQL Server Query Analyzer. While it says it updated the rows (all 500), all the launch_date fields were null. Is this the query or how the data fields are defined? Here are the particulars:
Column_name data_type type_name Column_size
launch_month 12 varchar 2
launch_year 12 varchar 4
launch_date 93 datetime 23