update using subquery in the where clause 1

[snowneil]

I have got this select statement so far..

SELECT *
FROM pContacts INNER JOIN
pContactsExtra ON pContacts.Con_SysID = pContactsExtra.ConExt_SysID
WHERE (pContacts.Con_DateOfBirth > CONVERT(DATETIME, '2006-03-01 00:00:00', 102)) AND (pContacts.Con_DateOfBirth < CONVERT(DATETIME,
'2006-03-29 00:00:00', 102)) AND (pContacts.Con_RecordStatus = 0)

I want to do...

UPDATE pContactsExtra
SET ConExt_Display = 'No'
WHERE (SQL Statement above)

Not sure how to correctly use the select statement above in the where clause.

Any help would be good.

 

[PHV]

you may try this:
UPDATE pContactsExtra
SET ConExt_Display = 'No'
WHERE NameOfPrimaryKey IN (
SELECT pContactsExtra.NameOfPrimaryKey
FROM pContacts INNER JOIN pContactsExtra ON pContacts.Con_SysID = pContactsExtra.ConExt_SysID
WHERE pContacts.Con_DateOfBirth > CONVERT(DATETIME, '2006-03-01 00:00:00', 102)
AND pContacts.Con_DateOfBirth < CONVERT(DATETIME, '2006-03-29 00:00:00', 102)
AND pContacts.Con_RecordStatus = 0
)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[snowneil]

Thanks, that worked great!