Re: sql to update where all related records meet criteria

From: Ed Prochak <ed.prochak_at_magicinterface.com>
Date: 1 Nov 2005 12:38:26 -0800
Message-ID: <1130877506.794596.191830@g14g2000cwa.googlegroups.com>

YoMama wrote:
> I need help writing a sql statement. The table structures are as
> follows:
> version table = version_pk, stage_fk
> section table = section_pk, version_fk, stage_fk
>
> There is a one-to-many relationship from version to section.
>
> The query needs to update version.stage_fk=61 where ALL of a
> version's related section.stage_fks=61 (if any one section for a
> given version has a stage_fk other than 61, the version should NOT
> change.
>
> Any help you can provide is greatly appreciated!!

HINT: try writing a SELECT statement that returns the result rows you seek. For your example it should either return one row or none.

and translating your problem description to SQL is a matter of seeing certain patterns. So for example, the condition to NOT change version will likely be written as a NOT EXISTS clause.

HTH
ed Received on Tue Nov 01 2005 - 14:38:26 CST