Asked  7 Months ago    Answers:  5   Viewed   61 times

I am trying to delete from a few tables at once. I've done a bit of research, and came up with this

DELETE FROM `pets` p,
            `pets_activities` pa
      WHERE p.`order` > :order
        AND p.`pet_id` = :pet_id
        AND pa.`id` = p.`pet_id`

However, I am getting this error

Uncaught Database_Exception [ 1064 ]: 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 'p, pets_activities pa...

I've never done a cross table delete before, so I'm inexperienced and stuck for now!

What am I doing wrong?

 Answers

30

Use a JOIN in the DELETE statement.

DELETE p, pa
      FROM pets p
      JOIN pets_activities pa ON pa.id = p.pet_id
     WHERE p.order > :order
       AND p.pet_id = :pet_id

Alternatively you can use...

DELETE pa
      FROM pets_activities pa
      JOIN pets p ON pa.id = p.pet_id
 WHERE p.order > :order
   AND p.pet_id = :pet_id

...to delete only from pets_activities

See this.

For single table deletes, yet with referential integrity, there are other ways of doing with EXISTS, NOT EXISTS, IN, NOT IN and etc. But the one above where you specify from which tables to delete with an alias before the FROM clause can get you out of a few pretty tight spots more easily. I tend to reach out to an EXISTS in 99% of the cases and then there is the 1% where this MySQL syntax takes the day.

Tuesday, June 1, 2021
 
McAn
answered 7 Months ago
84

To do this you need an outer join. By the way, the way you are writing your query with an implicit join is outdated and no longer recommended. Using the JOIN keyword is recommended. This also makes it easier to change an inner join to an outer join.

FROM categories AS sc
LEFT JOIN products AS s
ON s.ownerid=sc.id

To return 0 instead of NULL use IFNULL(..., 0). The entire query becomes:

SELECT
    sc.*,
    IFNULL(MIN(s.price), 0) AS minp,
    IFNULL(MAX(s.price), 0) AS maxp
FROM categories AS sc
LEFT JOIN products AS s
ON s.ownerid = sc.id
GROUP BY sc.id

You may also want to consider if it would be better to return the default NULL instead of 0 for categories that have no products.

Wednesday, June 2, 2021
 
redrom
answered 7 Months ago
33

Nope, you'd need to run multiple statements.

Because you need to delete from two tables, consider creating a temp table of the matching ids:

SELECT U.Id INTO #RecordsToDelete
FROM Users U
   JOIN LinkingTable J ON U.Id = J.U_Id
...

And then delete from each of the tables:

DELETE FROM Users 
WHERE Id IN (SELECT Id FROM #RecordsToDelete)

DELETE FROM LinkingTable
WHERE Id IN (SELECT Id FROM #RecordsToDelete)
Thursday, June 3, 2021
 
Zigglzworth
answered 7 Months ago
19

Assuming these are all generic names (table will not be a good table name), the problem is you can't use == for comparison. You are also missing some key syntax (DECLARE, SELECT INTO, etc.).

Change to this:

CREATE FUNCTION func01(value1 INT , monto DECIMAL (10,2))
RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
  DECLARE var_name DECIMAL(10,2);
  SET var_name = 0;
  SELECT if(value1 = 1,monto *divisa_dolar,monto *divisa_euro) INTO var_name
    FROM table
    WHERE data_init = 1;
  RETURN var_name;
END

MySQL Comparison Functions and Operators

Related Question: Single Equals in MYSQL

Function Help: http://www.databasejournal.com/features/mysql/article.php/3569846/MySQL-Stored-Functions.htm

Thursday, July 29, 2021
 
osondoar
answered 5 Months ago
28

I believe desc is reserved. It is used in an ORDER BY clause

You may be able to get away with using it if you put back-ticks around it, but I think you would be better off changing the name to a non-reserved word.

Saturday, July 31, 2021
 
davidb
answered 5 Months ago
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :
 
Share