Updating a date with a correlated subquery

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Updating a date with a correlated subquery

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-11-08 : 08:21:22

Randy writes "I need to take a month and day field in a SQL Server 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) != ' '

"

nr
SQLTeam MVY

12543 Posts

Posted - 2004-11-08 : 08:52:29

In the subquery you need B.product_key = pd_products.product_key
otherwise it is not a correlated subquery - product_key will default to the table inside the qubquery not the one outside.

But this would have given you a multiple value error so the where clause
where substring(launch_year,1,1) != ' '
and substring(launch_month,1,1) != ' '
must be excluding all values (apart from one maybe)

Is it updating everything to null?
Don't see how this could work unless launch_date is null for all recs bar 1.

try instead

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

but this is the same as
UPDATE dbo.pd_products
SET launch_date = cast( launch_year + '-' + launch_month + '-' + '1' AS datetime)
from dbo.pd_products
where substring(launch_year,1,1) != ' '
and substring(launch_month,1,1) != ' '

so I suspect there is something you are not telling us

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources