MySQL Forums
Forum List  »  General

Re: On update doesn't work
Posted by: Peter Brawley
Date: September 07, 2021 02:49PM

Multiple syntax errors in the script

And a DDL statement implicitly commits, so your transaction block here is meaningless

And the Insert commands have too many values for the columns they list

And they refer to non-existent referenced key values

When these problems are fixed and the ddl is simplified to just what's required ...

drop table if exists grupos;
CREATE TABLE `grupos` ( 
`id_grupo` int primary key auto_increment, 
`grupo` varchar(255) NOT NULL, 
`prueba` int DEFAULT NULL ,
 FOREIGN KEY (`prueba`) REFERENCES `grupos` (`id_grupo`) 
   ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB; 

INSERT INTO `grupos` (`id_grupo`, `grupo`, `prueba`) VALUES 
(1, 'a', NULL), 
(2, 'b', NULL), 
(3, 'c', NULL), 
(4, 'd', 3);

select * from grupos;
update grupos set prueba=2 where prueba=3;
select * from grupos;
delete from grupos where id_grupo=2;
select * from grupos;

... we get these results ...

drop table if exists grupos;
Query OK, 0 rows affected (0.02 sec)

localhost.test:
CREATE TABLE `grupos` (
    -> `id_grupo` int primary key auto_increment,
    -> `grupo` varchar(255) NOT NULL,
    -> `prueba` int DEFAULT NULL ,
    ->  FOREIGN KEY (`prueba`) REFERENCES `grupos` (`id_grupo`)
    ->    ON DELETE SET NULL ON UPDATE CASCADE
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.03 sec)

localhost.test:

localhost.test:
INSERT INTO `grupos` (`id_grupo`, `grupo`, `prueba`) VALUES
    -> (1, 'a', NULL),
    -> (2, 'b', NULL),
    -> (3, 'c', NULL),
    -> (4, 'd', 3);
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

localhost.test:

localhost.test:
select * from grupos;
+----------+-------+--------+
| id_grupo | grupo | prueba |
+----------+-------+--------+
|        1 | a     |   NULL |
|        2 | b     |   NULL |
|        3 | c     |   NULL |
|        4 | d     |      3 |
+----------+-------+--------+
4 rows in set (0.00 sec)

localhost.test:
update grupos set prueba=2 where prueba=3;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

localhost.test:
select * from grupos;
+----------+-------+--------+
| id_grupo | grupo | prueba |
+----------+-------+--------+
|        1 | a     |   NULL |
|        2 | b     |   NULL |
|        3 | c     |   NULL |
|        4 | d     |      2 |
+----------+-------+--------+
4 rows in set (0.00 sec)

localhost.test:
delete from grupos where id_grupo=2;
Query OK, 1 row affected (0.00 sec)

localhost.test:
select * from grupos;
+----------+-------+--------+
| id_grupo | grupo | prueba |
+----------+-------+--------+
|        1 | a     |   NULL |
|        3 | c     |   NULL |
|        4 | d     |   NULL |
+----------+-------+--------+
3 rows in set (0.00 sec)

What's the problem?

Options: ReplyQuote


Subject
Written By
Posted
September 07, 2021 01:23PM
Re: On update doesn't work
September 07, 2021 02:49PM
September 08, 2021 03:18AM
September 08, 2021 03:29AM
September 08, 2021 08:46AM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.