0

I'm new to mySQL,while learning about joins, I tried to make a full join using left join, right join and full outer join. then I realised one of my original tables ( table t2) is having a null row.

When try to delete it I get the error: Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column To disable safe mode, toggle the option in Preferences -> SQL Editor and reconnect.

the codes I ran is:

create table t1( c1 integer, c2 integer, c3  varchar (10));
create table t2( c1 integer, c2 integer, c3 varchar(10));

insert into t1 values ( 1, 2, "foo"), (2,3, "bar"),(3,8,"random");
insert into t2 values ( 1, 4, "jack"), (2,6,"jill"), (4,9,"hill");
alter table t2 add primary key (c1);
update  t2 set c1 = 3 where c1 = 4; 
delete from t2 where c1 is null;
select * from t1;
select * from t2;
select * from t1 left join t2 on t1.c1=t2.c1 
union
select * from t1 right join t2 on t1.c1=t2.c1;

and the output in MySQL Workbench v8.0 without 

 delete from t2 where c1 is null;

is something like this:

t1
+------+------+--------+
| c1   | c2   | c3     |
+------+------+--------+
|    1 |    2 | foo    |
|    2 |    3 | bar    |
|    3 |    8 | random |
+------+------+--------+

t2
+----+------+------+
| c1 | c2   | c3   |
+----+------+------+
|  1 |    4 | jack |
|  2 |    6 | jill |
|  3 |    9 | hill |
|NULL| NULL | NULL |
+----+------+------+

t1 union t2
+------+------+--------+------+------+------+
| c1   | c2   | c3     | c1   | c2   | c3   |
+------+------+--------+------+------+------+
|    1 |    2 | foo    |    1 |    4 | jack |
|    2 |    3 | bar    |    2 |    6 | jill |
|    3 |    8 | random |    3 |    9 | hill |
+------+------+--------+------+------+------+

Is this a bug or something wrong with my codes? How do I fix this? All inputs appreciated.Thanks

Update: when I use MySQL command line , I don't see the null row, but I see it when I run the script in workbench. Also I updated my codes based on the answers but I'm not able to add pictures as of now, as I'm new, I'm not allowed to do add pictures :-( , below are the new codes.

create table t1( c1 integer, c2 integer, c3  varchar (10), primary key(c1));
create table t2( c1 integer, c2 integer, c3 varchar(10),primary key (c1));

insert into t1 values ( 1, 2, "foo"), (2,3, "bar"),(3,8,"random");
insert into t2 values ( 1, 4, "jack"), (2,6,"jill"), (4,9,"hill");
/*alter table t2 add primary key (c1);*/
update  t2 set c1 = 3 where c1 = 4; 
delete from t2 where c1 is null;
select * from t1;
select * from t2;
select * from t1 left join t2 on t1.c1=t2.c1 
union
select * from t1 right join t2 on t1.c1=t2.c1;
V Kishore
  • 1
  • 1

2 Answers2

0

just use before query run below code in workbench query editor

SET SQL_SAFE_UPDATES = 0;
Zaynul Abadin Tuhin
  • 31,407
  • 5
  • 33
  • 63
  • You are correct. I can do that. But the error states that I'm not using a where condition with a key column. Although I'm using the column C1 in table t2 as primary key . I did that by using alter table line. So doesn't that make the query safe? – V Kishore Jul 30 '18 at 10:08
  • @VKishore For details about please read this documentation https://bugs.mysql.com/bug.php?id=56351 – Zaynul Abadin Tuhin Jul 30 '18 at 10:46
  • @VKishore https://www.xpertdeveloper.com/mysql-safe-update/ you can also read it – Zaynul Abadin Tuhin Jul 30 '18 at 10:47
0

Finally found an answer, seems like when we add primary key for a table, workbench add this null row at the end, which is actually not part of the tables. But we can double click on this NULL to enter values into table manually.

I'm not sure why I was getting the error: Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column To disable safe mode, toggle the option in Preferences -> SQL Editor and reconnect.

But I'm able to delete any other row if I want to. like when I use delete from t2 where c1 = 3; it works.

My expectation is that delete from t2 where c1 is null; should also work,except that it shouldn't make any changes to the table, but still it should have executed without errors. I'll post about this particular thing as soon as I get to know about this one.

V Kishore
  • 1
  • 1