FW: [dba-SQLServer] Update using 'Select (TOP or COUNT) SQL String':

Sorry for the double spacing.  I'm using the standard approach below (1 or 0
for the flag) for now, but I'm still curious whether I can use a select
within the SET portion of an UPDATE statement?

UPDATE	PLAN_STAGE.dbo.S2B_MD_DT_CHANGES
SET	MD_CHANGES = 1
WHERE (SELECT COUNT(1) FROM PLAN_STAGE.dbo.DATA_SOURCE_ACCOUNT_TMP) > 0

Regards,
Ron Moore

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Ron Moore
Sent: Friday, November 19, 2004 10:17 AM
To: dba-sqlserver at databaseadvisors.com
Subject: [dba-SQLServer] Update using 'Select (TOP or COUNT) SQL String':

I'm trying to stuff a flag when changes exist in metadata and have been
unsuccessful using a SELECT TOP 1 fieldname, as well as SELECT
COUNT(fieldname).  Currently trying the count approach as that would give me
the magnitudes of changes, and the SQL string looks like:

 

UPDATE           PLAN_STAGE.dbo.S2B_MD_DT_CHANGES

SET      MD_CHANGES = (SELECT COUNT(CHILD)

                                    FROM
PLAN_STAGE.dbo.DATA_SOURCE_ACCOUNT_TMP

                                    WHERE CHILD IS NOT NULL)

FROM   PLAN_STAGE.dbo.S2B_MD_DT_CHANGES,
PLAN_STAGE.dbo.DATA_SOURCE_ACCOUNT_TMP

 

I have also tried substituting the following WHERE EXISTS in place of the
last FROM line in above SQL string:

 

WHERE EXISTS            (SELECT COUNT(CHILD)

                                    FROM
PLAN_STAGE.dbo.DATA_SOURCE_ACCOUNT_TMP

                                    WHERE CHILD IS NOT NULL)

 

The select portion by itself works, but the update returns '(0 row(s)
affected)'.  What am I missing here?

Thanks in Advance,

Ron Moore 

 

_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com