Posted by Ed Murphy on 05/21/07 15:28

albertleng wrote:

> Let say i have a table A with the following 3 fields. 1)Date
> 2)Title 3)Status.

I trust that these aren't the real column names. Celko will likely
come along later today and post his usual lecture.

> i need to insert into table B (also have the same 3 fields) from this
> table A with the condition where Title is "Alarm" and Status is "ON".
> This can be done by a simple "INSERT" query.
>
> However, there's a case for table A in like below:
> Date Title Status
> ------ ------ ----------
> 5/7/07 1:05:23am ALARM ON
> 5/7/07 1:05:24am ALARM ON
> 5/7/07 1:05:25am ALARM ACK
> 5/7/07 1:05:25am ALARM ON
>
> Based on the table A above, i only need to insert from table A into
> table B the first 2 ALARM(ON)s. The third ALARM(ON) which has the same
> Date with ALARM(ACK) needs not to be inserted into table B.
>
> How can i write a simple SQL query which can insert all ALARM(ON)s
> which doesnt have same date with ALARM(ACK)?

insert into B (Date, Title, Status)
select Date, Title, Status
from A a1
where Title = 'ALARM'
and Status = 'ON'
and not exists (
select *
from A a2
where a2.Date = a1.Date
and a2.Title = a1.Title
and a2.Status = 'ACK'
)

[Back to original message]