UPDATE and SELECT in MySQL

Question

Can anyone help me on how can I update one table having a selection to another table in WHERE clause..

My query is looks like this but it is an error..

UPDATE empinfo e SET e.tellno='32154'
 WHERE e.empno IN (SELECT ei.empno FROM empinfo ei WHERE ei.tellno <> '123456');

Your response is highly appreciated.. :)

Answer 1

why do you need nested query here, try directly

UPDATE empinfo e SET e.tellno='32154'
WHERE e.tellno != '123456'

Answer 2

why not:

UPDATE empinfo e SET e.tellno='32154' WHERE tellno <> '123456'

Answer 3

What's wrong with:

UPDATE empinfo e SET e.tellno='32154' WHERE e.tellno <> '123456';

or

UPDATE empinfo e SET e.tellno='32154' WHERE e.tellno != '123456';

or

UPDATE empinfo e SET e.tellno='32154' WHERE e.tellno NOT '123456';

? However, if empno is not unique in your table then this SQL won't work like the one you've provided! So, is empno unique or not?

Answer 4

Not sure why I'm answering this as you acceptence rate is rubbish but here goes.

Try this

UPDATE empinfo SET tellno='32154'
WHERE empno IN (
     SELECT empno 
     FROM empinfo 
     WHERE tellno NOT '123456'
);

Answer 5

Okay, example:

REC1: empno=1 tellno='654321'
REC2: empno=2 tellno='654321'
REC3: empno=3 tellno='123456'
REC4: **empno=1** tellno='123456'

When you use something like

UPDATE empinfo e SET e.tellno='32154' WHERE e.tellno != '123456';

Then you will get this:

REC1: empno=1 tellno='32154'
REC2: empno=2 tellno='32154'
REC3: empno=3 tellno='123456'
**REC4: empno=1 tellno='123456'**

However, your original query seems to want to change it to this:

REC1: empno=1 tellno='32154'
REC2: empno=2 tellno='32154'
REC3: empno=3 tellno='123456'
**REC4: empno=1 tellno='32154'**

Which of these two options did you want? If you want the second one, then you'll need to do a sub-select but a subselect on the same table isn't possible with MySQL.