MySQL update query on multiple rows

Question

I need to update my database its a soft delete on multiple row based in locationId

update device d
set d.deleteDate='2016-05-07'
where d.id in (select dtg.deviceId from devicestogroups dtg
               where dtg.groupId in (select g.id from `group` g
                                     where g.locationId ='1'));

When I run the query 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.

Subquery will return me 2 records as I know because subquery returns two record update will not happen, but how do I solve this issue. I tried to solve but not successful.

Shushil Bohara · Accepted Answer · 2016-12-05 11:41:31Z

2

Try this, it would more better to use join instead of sub query with IN:

UPDATE device d
INNER JOIN devicestogroups dtg ON dtg.deviceId = d.id
INNER JOIN `group` g ON g.id = dtg.groupId
    AND g.locationId ='1'
SET d.deleteDate='2016-05-07'

OR

UPDATE device d
INNER JOIN devicestogroups dtg ON dtg.deviceId = d.id
INNER JOIN `group` g ON g.id = dtg.groupId
SET d.deleteDate='2016-05-07'
WHERE g.locationId ='1'

answered Dec 5, 2016 at 11:41

Shushil Bohara

5,6662 gold badges19 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Piotr Rogowski · Accepted Answer · 2016-12-05 11:40:09Z

0

try this:

update device set deleteDate='2016-05-07' 
where id in (select dtg.deviceId from devicestogroups dtg where dtg.groupId in ( select g.id from `group` g where g.locationId ='1'));

if you execute update not using alias on column to update

answered Dec 5, 2016 at 11:40

Piotr Rogowski

3,90021 silver badges24 bronze badges

1 Comment

suresh Over a year ago

Thanks for your response but still the query fail to update for same reason

Jibin Balachandran · Accepted Answer · 2016-12-05 11:41:41Z

0

Try this:

update d
set d.deleteDate='2016-05-07'
from device d
inner join devicestogroups dtg on d.id=dtg.deviceId
inner join `group` g on g.id =dtg.groupId
where g.locationId ='1'

answered Dec 5, 2016 at 11:41

Jibin Balachandran

3,4491 gold badge27 silver badges39 bronze badges

Collectives™ on Stack Overflow

MySQL update query on multiple rows

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related