sql - How do I update multiple rows in MySQL(AuroraDB)?

Question

               Table Mapper
|-------------------------------------------|
|   id    |   info_fk   |       reg_fk      |
|    1    |     100     |                   |
|    2    |     101     |                   |
|    3    |     101     |                   |
|    4    |     102     |                   |
|    5    |     102     |                   |
|    6    |     103     |                   |
|-------------------------------------------|

       Table Region
|---------------------------|
|   id    |     region_id   |
|    1    |       A101      |
|    2    |       A102      |
|    3    |       A301      |
|    4    |       B101      |
|---------------------------|

             Table Info
|--------------------------------------|
|   id     |          address          |
|   100    |           A101            |
|   101    |         A102;A101         |
|   102    |         A301;B101         |
|   103    |           B101            |
|--------------------------------------|

I want to map info table's id and fill table Mapper. Above sample, my desired output is to make table Mapper to

                Table Mapper
|-------------------------------------------|
|   id    |   info_fk   |       reg_fk      |
|    1    |     100     |         1         |
|    2    |     101     |         1         |
|    3    |     101     |         2         |
|    4    |     102     |         3         |
|    5    |     102     |         4         |
|    6    |     103     |         4         |
|-------------------------------------------|

this. reg_fk means table Region's ID, and I select reg_fk based on table Info. table Mapper already exists based on number of table Info's address, so all I have to do is read Info's address and put proper ID to Mapper.

This SQL is what I tried:

with region_info (region_id, info_id) as (
    select region.id as region_id, info.id as info_id
    from Region region
             inner join Info info on
            info.address like concat(concat('%', region.region_id), '%')
)
update Mapper mapper inner join Info info
    on info_fk = info.id
set mapper.reg_fk = (
    case
        when info_fk = info.id then
            (select region_id
             from region_info
             where region_info.info_id = info.id
            )
        end
    )
where region_info.info_id = info.id;

It emits error, and I understood that I made a mistake that inside setting up mapper.reg_fk, there's multiple rows which I cannot set one 'mapper.reg_fk row' to multiple rows. But in this SQL sentence, if I join info and mapper on info_fk = info.id, then I should return multiple rows too. like in this example, info_fk = 100 doesn't make any error but info_fk = 101 then update 101 returns 2 rows. And also inside case sentence, I also get 2 rows (region_id 1 and 2) so it would be like

with join restriction, what to update in table Mapper
|-------------------------------------------|
|   id    |   info_fk   |       reg_fk      |
|    2    |     101     |                   |
|    3    |     101     |                   |
|-------------------------------------------|

what appears in case sentence
|--------------------|
|       reg_fk       |
|          1         |
|          2         |
|--------------------|

I want to properly update reg_fk to table Mapper. How do I do this?

Here you can simply build my example data and test my code (which is not working right now). http://sqlfiddle.com/#!9/8c0c7d/4

Thank you.

question from:https://stackoverflow.com/questions/66059125/how-do-i-update-multiple-rows-in-mysqlauroradb

Answer 1

with region_info (region_id, info_id) as (
    select region.id as region_id, info.id as info_id
    from Region region
             inner join Info info on
            info.address like concat(concat('%', region.region_id), '%')
)
update Mapper mapper inner join Info info
    on info_fk = info.id inner join region_info on info.id = region_info.info_id
set mapper.reg_fk = (
            (select region_id
             from region_info
             where region_info.info_id = info.id
             LIMIT 1
            )
        )

last "where region_info.info_id = info.id;" is out of subquery. region_info is in subquery. so it can't find region_info. although you use "with" clause, if it is in "from" clause, you can't find it.

and you can use "LIMIT 1" or "any"(before subquery).