新人~~求助一个问题~~谢谢

[icyfantasy]

假设我有两个表：User（user_id, date_of_birth, city_id） 和 Message（message_id, message_type, posted）
想要删除Message表中年龄低于21岁且city_id=1发帖时间超过一年的公共留言（message_id=1）
但是遇到了问题，我试了试只能确定错在哪了（红字），但不知道该怎么做，希望达人帮忙，最好可以不使用subquery

delete from Message
USING Message,User
where
owner_id =(select user_id from User
where datediff(curdate(),date(date_of_birth))<365*21
)
AND
message_type=1
AND
datediff( curdate(),date(posted) )>365
AND
city_id=1

[--小F--]

delete from
 Message USING Message,User
where
 owner_id =(select user_id from User where datediff(dd,curdate(),date(date_of_birth))<365*21
)
AND
  message_type=1
AND
  datediff( dd,curdate(),date(posted) )>365
AND
  city_id=1

[xuam]

owner_id in (select user_id from User
where datediff(curdate(),date(date_of_birth))<365*21
)

[duoxu1983]

=变成in

[AcHerat]

user_id可能会有多个，不能=，用in试试！

[水族杰纶]

delete from Message
USING Message,User
where

神马意思？

[dawugui]

delete from Message where city_id=1 and message_id = 1 and 
(datediff(yy,posted ,getdate()) > 1 or (datediff(yy,posted ,getdate()) = 1 and right(convert(varchar(10),posted,120),5) > right(convert(varchar(10),getdate(),120),5)))
and owner_id = (select user_id from user where datediff(yy,date_of_birth,getdate()) < 21 or (datediff(yy,date_of_birth ,getdate()) = 21 and right(convert(varchar(10),date_of_birth,120),5) < right(convert(varchar(10),getdate(),120),5)))

[Hankuker]

[Quote=引用 5 楼 wufeng4552 的回复:]
SQL code
delete from Message
USING Message,User
where

神马意思？
[/Quote]
++

[dawugui]

delete Message from Message m , User n
where m.owner_id = n.user_id and 
city_id=1 and message_id = 1 and 
(datediff(yy,posted ,getdate()) > 1 or (datediff(yy,posted ,getdate()) = 1 and right(convert(varchar(10),posted,120),5) > right(convert(varchar(10),getdate(),120),5)))
(datediff(yy,date_of_birth,getdate()) < 21 or (datediff(yy,date_of_birth ,getdate()) = 21 and right(convert(varchar(10),date_of_birth,120),5) < right(convert(varchar(10),getdate(),120),5)))

你那个求一年,或是求21岁以下的不能那么做.