Delete all records in Table 1 where related record has value in Ta

I believe this should achieve what you want to do, just replace 5 with
whatever value you want:
DELETE a FROM table1 a JOIN table2 b ON (a.ID_Table2 = b.id) AND (b.value =
5)
Adam J Warne, MCDBA
"Imager" wrote:

> Simple query for you query gurus here:
> Given two tables, related by ID_Adjacency, how can I delete all rows in
> table 1, where the related row in table 2 has some given value? ie.: with
> the following two tables, delete all records from Table1, where the relate
d
> record in Table 2 has a "Value" field with a value of 2.
>
> CREATE TABLE [dbo].[Table2] (
> [ID] [int] IDENTITY (1, 1) NOT NULL ,
> [Value] [tinyint] NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[Table1] (
> [ID] [int] IDENTITY (1, 1) NOT NULL ,
> [ID_Table2] [int] NOT NULL ,
> [Value] [nvarchar] (50) COLLATE Latin1_General_CI_AS NOT NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Table2] WITH NOCHECK ADD
> CONSTRAINT [PK_Table2] PRIMARY KEY CLUSTERED
> (
> [ID]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Table1] WITH NOCHECK ADD
> CONSTRAINT [PK_SourceTable] PRIMARY KEY CLUSTERED
> (
> [ID]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Table1] ADD
> CONSTRAINT [FK_Table1_Table2] FOREIGN KEY
> (
> [ID_Table2]
> ) REFERENCES [dbo].[Table2] (
> [ID]
> ) ON DELETE CASCADE ON UPDATE CASCADE
> GO
>
>On Tue, 16 Aug 2005 03:39:02 -0700, Adam Warne wrote:
>I believe this should achieve what you want to do, just replace 5 with
>whatever value you want:
>DELETE a FROM table1 a JOIN table2 b ON (a.ID_Table2 = b.id) AND (b.value = 5)[/col
or]
Or the more protable ANSI-stnadard version:
DELETE FROM table1
WHERE EXISTS (SELECT *
FROM table2
WHERE table2.ID = table1.ID_Table2
AND table2.value = 5)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks chaps.
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:5mf4g19hbk4rtf9khjg7t428kllhgeudsc@.
4ax.com...
> On Tue, 16 Aug 2005 03:39:02 -0700, Adam Warne wrote:
>
> Or the more protable ANSI-stnadard version:
> DELETE FROM table1
> WHERE EXISTS (SELECT *
> FROM table2
> WHERE table2.ID = table1.ID_Table2
> AND table2.value = 5)
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)sql