Tag: tables

Creating a trigger to update one table’s row based on another update from a different table’s row

So, I have the following two tables:

CREATE TABLE IF NOT EXISTS `services` (   `services_id` INT NOT NULL AUTO_INCREMENT,   `staff_num` INT NOT NULL,   `health_and_wellness` INT NOT NULL,   `tutoring` INT NOT NULL,   PRIMARY KEY (`services_id`),   UNIQUE INDEX `services_id_UNIQUE` (`services_id` ASC) VISIBLE,   INDEX `fk_services_healthandwell_idx` (`health_and_wellness` ASC) VISIBLE,   INDEX `fk_services_tutoring_idx` (`tutoring` ASC) VISIBLE,   CONSTRAINT `fk_services_tutoring`     FOREIGN KEY (`tutoring`)     REFERENCES `tutoring` (`tutoring_id`)     ON DELETE CASCADE     ON UPDATE CASCADE,   CONSTRAINT `fk_services_healthandwell`     FOREIGN KEY (`health_and_wellness`)     REFERENCES `health and wellness` (`health_and_wellness_id`)     ON DELETE CASCADE     ON UPDATE CASCADE) ENGINE = InnoDB;

and

CREATE TABLE IF NOT EXISTS `tutoring` (   `tutoring_id` INT NOT NULL AUTO_INCREMENT,   `subject` INT NOT NULL,   `staff_members` INT NOT NULL,   PRIMARY KEY (`tutoring_id`),   UNIQUE INDEX `tutoring_id_UNIQUE` (`tutoring_id` ASC) VISIBLE,   INDEX `fk_tutoring_subject_idx` (`subject` ASC) VISIBLE,   CONSTRAINT `fk_tutoring_subject`     FOREIGN KEY (`subject`)     REFERENCES `subjects` (`subject_id`)     ON DELETE NO ACTION     ON UPDATE NO ACTION) ENGINE = InnoDB;

I want to create a trigger that updates services.staff_num whenever tutoring.staff_members is updated.. I’ve tried a couple different triggers, but each time nothing happens or I get an error.. My latest attempt was this:

DELIMITER $  $   CREATE TRIGGER UpdateStaffNum AFTER UPDATE ON collegedb.tutoring FOR EACH ROW     BEGIN         UPDATE services         SET services.staff_num = services.staff_num + tutoring.staff_members;  END;

Which of course would be too simple too work, heh. Anyone have any suggestions on this? Thanks.

How do I get a list of all tables in a mysql db, ordered by foreign key dependencies?

I want a list of tables, ordered by foreign key. So if table "Book" has a FK dependency on table "Author", Author would be listed first and Book second. I have no cyclic dependency loops but I do have a few self-join relationships (table has an FK dependency on itself). I would like a script to generate this list. (Or perhaps a recursive CTE in mysql 8?)

I can get the foreign key dependencies from: (mysql query)

select CONSTRAINT_NAME, TABLE_NAME, REFERENCED_TABLE_NAME from information_schema.referential_constraints where constraint_schema = 'DBName' order by TABLE_NAME;

And the table-list from:

show full tables where table_type <> "VIEW";

Background: I would like to copy my mysql db-schema into an in-memory H2 database for testing. But H2 does not support full mysql syntax. In particular FOREIGN_KEY_CHECKS=0 is not supported and H2 SET REFERENTIAL_INTEGRITY FALSE only applies to insert/update-constraint-checks not table-creation. So my tables need to be created in the correct order, according to FK dependencies.

I can dump the schema with mysqldump. But mysqldump does not order the table creation by foreign keys. If table "Child" has a FK dependency on table "Parent", mysqldump appears to put the table-creation in alphabetical order so Child will be created before Parent even though it depends on Parent. It relies on disabling foreign key checks while all the tables are created.

So if I had a list of table names, in fk order, I could pass that to mysqldump to dump the schema.

Union All of two tables based on month of a date in Oracle

I have two tables table_a and table_b as below. column invdate is of date datatype and column amount is of number datatype.

table_a

invdate amount
20-01-2021 50
20-01-2021 100
20-02-2021 50
20-03-2021 50

table_b

invdate amount
01-01-2021 250
01-02-2021 300
01-03-2021 40
01-03-2021 50

I am doing a UNION ALL of both tables to get the sum of the amounts based on only month and year.

SELECT to_char(invdate, 'MM-YYYY') as "Date", sum(amount) as "Total" FROM ( SELECT to_char(invdate, 'MM-YYYY'), amount FROM table_a UNION ALL SELECT to_char(invdate, 'MM-YYYY'), amount FROM table_b ) GROUP BY to_char(invdate, 'MM-YYYY') ORDER BY to_char(invdate, 'MM-YYYY') asc;

to get the final output like below

Date Total
01-2021 400
02-2021 350
03-2021 140

but it gives me the below error.

ORA-00904: "INVDATE": invalid identifier 00904. 00000 -  "%s: invalid identifier" *Cause:     *Action: Error at Line: 10 Column: 18

What am I doing wrong here?

WordPress DROP tables with prefix

Hi I am trying to drop some tables in bulk before creating them again for testing purposes.

I am trying to run this statement

dbDelta(       "SET GROUP_CONCAT_MAX_LEN=10000;       SET @tbls = (SELECT GROUP_CONCAT(TABLE_NAME)       FROM information_schema.TABLES       WHERE TABLE_SCHEMA = 'wp_test'       AND TABLE_NAME LIKE 'mch_%');       SET @delStmt = CONCAT('DROP TABLE ',  @tbls);       PREPARE stmt FROM @delStmt;       EXECUTE stmt;       DEALLOCATE PREPARE stmt;",       $  wpdb->prefix )

But nothing happens, my plugin gets activated with no errors, but the tables are still there.

I am new to this stuff, so I would like to understand the proper way to do this.

6 random character prefix automatically added to entire DB tables, how / why?

I’m working on a site, live on server. I’m using Navicat for working with the DB. I have the WP DB opened and am 100% sure the tables were just standard tables, using standard wp_ prefix.

While I’m working on another screen in admin area, I needed some user-meta info, I refresh the already opened DB.. and suddenly ALL tables have 6 random character prefix automatically added to entire DB tables!

So now instead of having one table.. wp_usermeta
I now have.. aojsfj_wp_usermeta

Now, opening for example aojsfj_wp_usermeta is not a list of users, it’s just ONE user’s meta. So now the DB has created all separate tables per the few (7) test user accounts, prefixing them with 6 random characters.

Can someone enlighten me on why / how this has happened? So far everything still seems to be working, but the DB + all these new tables can’t be good!

Making Tables accessible on WordPress

How do I make these tables accessible on WordPress

<!-- wp:paragraph --> <p>By Kate Deibel, PhD, Inclusion &amp; Accessibility Librarian</p> <!-- /wp:paragraph --> <!-- wp:paragraph --> <p>Every year, the third Thursday of May marks <a href="https://globalaccessibilityawarenessday.org/events/">Global Accessibility Awareness Day</a> (GAAD), a day to teach and focus on digital access and inclusion. Accessibility is an every day activity that should be incorporated into your work...
Code (markup):

Making Tables accessible on WordPress

MS SQL Creating Tables and Adding Values by a Trigger

I have a table for products. They have product name, id, and quantity. By using a trigger whenever I insert a product to this table for example id =145, quantity= 10 I want that existing table get updated and the quantity of the item with an existing id will be decreased by one and autmatically by the trigger we will create a new table only 2 columns, id and quantity, and it will be id=145, quantity =10. How can I do this?

How to create table from two alias tables with sql

I want to create a table that shows a name, position week1, position week2. I created the following queries that give the correct result for each week separately. All the data is in one table results. Server version: 10.4.14-MariaDB

 SELECT     name, team, points,week,      @curRank := @curRank + 1 AS position     FROM results,  (SELECT @curRank := 0) r     WHERE week = 1     order by points DESC

and

SELECT     name, team, points,week,      @prevRank := @prevRank + 1 AS position2     FROM results,  (SELECT @prevRank := 0) r2     WHERE week = 2 ORDER BY points DESC

But when I combine the with UNION I get an incorrect result.

(SELECT     name, team, points,week,      @curRank := @curRank + 1 AS position     FROM results,  (SELECT @curRank := 0) r     WHERE week = 1)       UNION  (SELECT     name, team, points,week,      @secondRank := @secondRank + 1 AS position2     FROM results,  (SELECT @secondRank := 0) r2     WHERE week = 2) ORDER BY points

So how would I combine the two select statements to get the table with just name, position week1, position week2? I do not need points in the table, but points are used to calculate the position.

How to get count of an object, through 3 different tables in postgres with ID’s stored in each table

I’m currently using Postgres 9.6.16.

I am currently using 3 different tables to store a hypothetical users details.

The first table, called contact, this contains:

ID, Preferred_Contact_Method

The second table, called orders, This contains:

ID, UserID, Contact_ID (the id of a row, in the contact table that relates to this order)

The Third Table, Called order_details

ID, Orders_ID (the id in the orders table that relates to this order details)

The tables contain other data as well, but for minimal reproduction, these are the columns that are relevant to this question.

I am trying to return some data so that i can generate a graph, in this hypothetical store, There’s only three ways we can contact a user: Email, SMS, or Physical Mail.

The graph is supposed to be 3 numbers, how many mails, emails, and SMS we’ve sent to the user; since in this hypothetical store whenever you purchase something you get notified of the successful shipment, these methods are 1:1 to the order_details, so if there’s 10 order_detail rows for the same user, then we sent 10 tracking numbers, and since there can be multiple order_details (each item has a different row in order_details) in an order, we can get the count by counting the total rows of order details belonging to a single user/contact, then attributing to what kind of contact method that user preferred at the time of making that order.

To represent this better: If a new user makes a new order, and orders 1 apple, 1 banana, and 1 orange. For the apple, the user set preferred tracking number delivery as SMS, for the banana, they set it to EMAIL, for the orange, they thought it would be funny to set the tracking number delivery via MAIL. Now, i want to generate a graph to this users preferred delivery method. So i’d like to query all those rows and obtain:

SMS, 1 EMAIL, 1 MAIL, 1

Here’s a SQL Fiddle link with the schema and test data: http://sqlfiddle.com/#!17/eb8c0

the response with the above dataset should look like this:

method | count SMS,     4 EMAIL,   4 MAIL,    4

Table scan instead of index seeks happening when where clause filters across multiple tables in join using OR

We have an application generated query using a view that has two tables joined on a LEFT OUTER join. When filtering by fields from just one table (either table) an index seek happens and it’s reasonably fast. When the where clause includes conditions for fields from both tables using an OR the query plan switches to a table scan and doesn’t utilize any of the indexes.

All four fields that are being filtered on are indexed on their respective tables.

Fast query plan where I filter on 3 fields from one table: https://www.brentozar.com/pastetheplan/?id=Hym_4PRSO

Slow query plan where I filter on four fields…three from one table and one from another table: https://www.brentozar.com/pastetheplan/?id=r1dVNDRHO

Ideally I would like to understand why this is happening and how to nudge the query engine to utilize all the indexes.