0
votes

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;
2
I don't think this is reproducible.Tim Biegeleisen
@Tim I tried to delete the entire database I was using, using the command Drop. Successful! Then recreated everything, but I see the same problem.V Kishore

2 Answers

0
votes

just use before query run below code in workbench query editor

SET SQL_SAFE_UPDATES = 0;
0
votes

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.