Newbie needs help converting SELECT to DELETE 1

[mrcrusoe]

Hi,
I have created the following SELECT statement that works perfectly and retrieves the data I want to work with:

SELECT * FROM shoppingbasket
WHERE DateAdded < SUBDATE(NOW(), INTERVAL 30 MINUTE)
AND NOT EXISTS (
SELECT * FROM shoppingbasket newer
WHERE DateAdded >= SUBDATE(NOW(), INTERVAL 30 MINUTE)
AND shoppingbasket.SessionNo = newer.SessionNo)

However, what I want to do is DELETE the data it returns, could someone please help me with this, it's a bit beyond my current SQL skills!

Thanks.

 

[Golom]

[red]DELETE[/red] * FROM shoppingbasket
WHERE DateAdded < SUBDATE(NOW(), INTERVAL 30 MINUTE)
AND NOT EXISTS (
SELECT * FROM shoppingbasket newer
WHERE DateAdded >= SUBDATE(NOW(), INTERVAL 30 MINUTE)
AND shoppingbasket.SessionNo = newer.SessionNo)

 

[r937]

possibly without the asterisk, depending on which database it is ;-)

r937.com | rudy.ca

 

[mrcrusoe]

Thanks to Golom and r937 for responding.
Unfortunately the suggestions throw up the following errors:

DELETE * FROM shoppingbasket
WHERE DateAdded < SUBDATE(NOW(), INTERVAL 30 MINUTE)
AND NOT EXISTS (
SELECT * FROM shoppingbasket newer
WHERE DateAdded >= SUBDATE(NOW(), INTERVAL 30 MINUTE)
AND shoppingbasket.SessionNo = newer.SessionNo)

Error message from server:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '* FROM shoppingbasket
WHERE DateAdded < SUBDATE(NOW(), INTERVAL 30 MINUTE)
AND N' at line 1

and

DELETE FROM shoppingbasket
WHERE DateAdded < SUBDATE(NOW(), INTERVAL 30 MINUTE)
AND NOT EXISTS (
SELECT * FROM shoppingbasket newer
WHERE DateAdded >= SUBDATE(NOW(), INTERVAL 30 MINUTE)
AND shoppingbasket.SessionNo = newer.SessionNo)

Error message from server:
You can't specify target table 'shoppingbasket' for update in FROM clause


I'm trying to DELETE the data from the 'shoppingbasket' table. The 'newer' table created in the SELECT statement is a 'temporary' table created only for this SELECT statement so that data in the SessionNo columns can be compared.
I am using mySQL and the YourSQL front end program to work with my database.

Any more suggestions?
Thanks

 

[r937]

ah, mysql, you shoulda said ;-)

(and next time, you should post in forum436)

delete t1
  from shoppingbasket as t1
inner
  join shoppingbasket as t2 
    on t2.SessionNo = t1.SessionNo
   and t2.DateAdded >= SUBDATE(NOW(), INTERVAL 30 MINUTE)
 where t1.DateAdded < SUBDATE(NOW(), INTERVAL 30 MINUTE)
   and t2.SessionNo is null

r937.com | rudy.ca

 

[mrcrusoe]

Thank you so much r937 for this.

I had to change INNER JOIN to LEFT JOIN and it worked perfectly!
(I must admit it took me some time to work this out but I got there in the end)

And yes, I will post all future mySQL posts in forum436, thanks for that. I'm still new to this site but have been impressed with very helpful responses so far!

Thanks again.

 

[r937]

I had to change INNER JOIN to LEFT JOIN and it worked perfectly!
(I must admit it took me some time to work this out but I got there in the end)

congratulations for a job well done


if you didn't know of the IS NULL condition with LEFT OUTER JOIN, this was a heck of a good time for you to learn it on your own

thanks for covering up for my mistake

of course i meant to write LEFT OUTER JOIN, i don't know why i didn't



r937.com | rudy.ca