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?



Use a JOIN in the DELETE statement.

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

Alternatively you can use...

      FROM pets_activities pa
      JOIN pets p ON = p.pet_id
 WHERE p.order > :order
   AND p.pet_id = :pet_id 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
answered 7 Months ago

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

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

    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 =

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
answered 7 Months ago

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:

WHERE Id IN (SELECT Id FROM #RecordsToDelete)

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

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))
  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;

MySQL Comparison Functions and Operators

Related Question: Single Equals in MYSQL

Function Help:

Thursday, July 29, 2021
answered 5 Months ago

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
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 :