List:General Discussion« Previous MessageNext Message »
From:Stirling E. Olson Date:February 3 2000 10:52pm
Subject:Limited DELETE and UPDATE statements
View as plain text  
Previous to using MySQL 3.22, I used MS-SQL Server 6.5.  In SQL Server 6.5,
I used to be able to do statements like the following:

DELETE table1
from   table1 t1,
       table2 t2
where  t1.refno = t2.refno

or

UPDATE table1
set    somefield = "Hello"
from   table1 t1,
       table2 t2
where  t1.refno = t2.refno

These do not seem to work in MySQL.  Assuming that I haven't missed
something, how do I selectively delete records from a table?  As an example,
suppose that I have a detail table like the following:

create table HobbyDetail
    (
    PersonNum INTEGER,
    HobbyNum INTEGER
    )

I want to delete all entries in HobbyDetail where a person (PersonNum) has a
particular hobby (HobbyNum) ONLY when they also do another particular hobby.
In MS SQL Server 6.5, I would execute the following:

DELETE    HobbyDetail
FROM      HobbyDetail hd1,
          HobbyDetail hd2
WHERE     hd1.PersonNum = hd2.PersonNum
AND       hd1.HobbyNum = 3
AND       hd2.HobbyNum = 4

or I could have done the following:

SELECT    hd1.PersonNum
INTO      #temp
FROM      HobbyDetail hd1,
          HobbyDetail hd2
WHERE     hd1.PersonNum = hd2.PersonNum
AND       hd1.HobbyNum = 3
AND       hd2.HobbyNum = 4

DELETE    HobbyDetail
FROM      HobbyDetail hd,
          #temp t
WHERE     hd.PersonNum = t.PersonNum
AND       hd.HobbyNum = 3

Neither of these seem to work in MySQL.  How do I do this in MySQL?

Thanks,
Stirling


Thread
Limited DELETE and UPDATE statementsStirling E. Olson4 Feb
  • Re: Limited DELETE and UPDATE statementssasha4 Feb
    • RE: Limited DELETE and UPDATE statementsRoy Nasser4 Feb
  • Re: Limited DELETE and UPDATE statementssinisa4 Feb
  • Re: Limited DELETE and UPDATE statementsStirling E. Olson4 Feb