MySql Update Query

Question

I have a table in MySQL with following data

NAME    SEX  
A       Male  
B       Female  
C       Male  
A       Null  
B       Null  
C       Null  
D       Null

how can I update SEX from preceding rows?

Output:

NAME    SEX  
A       Male  
B       Female  
C       Male  
A       Male  
B       Female  
C       Male
D       Null

Thanks in advance

How would you like the values to be? An example of expected output would help... — OMG Ponies, Jan 17 '11 at 20:54
Question is not clear, do you mean you want to update `A` which is null from the other `A` which has a value? — VoodooChild, Jan 17 '11 at 20:55

score 3 · Accepted Answer · answered Jan 17 '11 at 21:05

3

If a name can have only one sex, you can update all other names that do not have a sex yet like:

update  YourTable yt1
join    (
        select  name
        ,       max(sex) as maxSex
        from    YourTable
        group by
                name
        ) yt2
on      yt1.name = yt2.name 
        and yt2.maxSex is not null
set     yt1.sex = yt2.maxSex
where   yt1.sex is null;

In your example, this will fill in the sex for each row except D, since there is no record with name D and a non-null sex.

answered Jan 17 '11 at 21:05

Andomar

232,371
49
380
404

It works fine. But there is an interesting thing. After running this query when I update any record in table manually in any IDE then it automatically updates all rows. Can you tell me please why is it? – Novice Jan 17 '11 at 21:25
@Novice: An update query has no effect after it's finished. Check if there are triggers on your table? – Andomar Jan 17 '11 at 21:26
I've checked triggers. I create another db and see what happens – Novice Jan 17 '11 at 21:37

score 1 · Answer 2 · answered Jan 17 '11 at 20:56

1

If you want to set Male on all fields where Null is, just run a simple update:

UPDATE table SET SEX='Male' WHERE SEX IS NULL

If you want to do this automatically, you should insert an incrementing index column to refer to in your script.

answered Jan 17 '11 at 20:56

Matten

17,365
2
42
64

score 0 · Answer 3 · edited May 23 '17 at 12:11

0

If I understand correctly what you want to do, a query like this will be ok

UPDATE TABLE T SET SEX = (SELECT SEX FROM TABLE WHERE NAME = T.NAME LIMIT 1);

but mysql cannot use in a subquery a table used in update, so you have to use a ugly trick like this one:

MySQL Error 1093 - Can't specify target table for update in FROM clause

edited May 23 '17 at 12:11

Community

1
1

answered Jan 17 '11 at 21:02

Ass3mbler

3,855
2
20
18

MySql Update Query

3 Answers3