Replace value in MySQL table by condition from two tables

Started by ruschan, Oct 05, 2022, 12:57 AM

Previous topic - Next topic

ruschanTopic starter

There are 2 tables available: Parents (ID card, name, age) and Children (ID, name, age, parent_id), where parent_id represents the ID of the corresponding parent.

To update the age of a child to 10, a query can be used that combines both tables. More specifically, we join the Parents and Children tables using their IDs and specify the condition to check if the child is more than 10 years old and their parent is less than 40 years old. The resulting query will update the age of the child to 10 only if the aforementioned conditions are met:

SELECT, p.age,, c.age
FROM parents p JOIN children c ON = c.parent_id
WHERE p.age < 40 AND c.age > 10

Regarding the IbExpert script, it aims to modify a product's price in a particular store by decreasing it by 10%. However, a mistake occurs during its execution. The script uses a JOIN statement to connect the Products and Stores tables using their IDs and then changes the price of the corresponding product. Nonetheless, the error indicates that one of the tables cannot be found or accessed. Further examination of the script and the database schema may help solve the issue.


I'm not entirely confident about whether Firebird can handle this query, but let's give it a try. I haven't worked with it for some time. If the query fails, we can always switch to the alternative solution.

The mentioned query targets the Incomes32 table and sets the value of the facialacc_cls attribute to 15,000,000 if it has the default, i.e., zero value and meets a specific condition. The condition involves checking if a corresponding record exists in the Quotestitle table with an acceptdate different from 20220000 and the same ID as the current record in Incomes32. To carry out this check, we use a subquery within the EXISTS clause.

Here is the original query for reference:

UPDATE incomes32
  SET facialacc_cls = 15000000
  WHERE facialacc_cls = 0 AND
                FROM quotestitle
                WHERE = incomes32.recordindex AND
                      quotestitle.acceptdate <> 20220000)

I hope this helps!