1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

update multiple rows with multiple columns using loop

Discussion in 'Databases' started by neilfurry, Dec 21, 2017.

  1. #1
    hi mate,

    i have this query statement

    UPDATE kai
    SET `0522` = CASE WHEN `0522` IS NULL OR '0' THEN '' ELSE '1' END,
    `0529` = CASE WHEN `0529` IS NULL OR '0' THEN '' ELSE '1' END,
    `0605` = CASE WHEN `0605` IS NULL OR '0' THEN '' ELSE '1' END,
    `0610` = CASE WHEN `0610` IS NULL OR '0' THEN '' ELSE '1' END,
    `0610b` = CASE WHEN `0610b` IS NULL OR '0' THEN '' ELSE '1' END,
    `0612` = CASE WHEN `0612` IS NULL OR '0' THEN '' ELSE '1' END,
    `0619` = CASE WHEN `0619` IS NULL OR '0' THEN '' ELSE '1' END,
    `0626` = CASE WHEN `0626` IS NULL OR '0' THEN '' ELSE '1' END,
    `0703` = CASE WHEN `0703` IS NULL OR '0' THEN '' ELSE '1' END,
    `0710` = CASE WHEN `0710` IS NULL OR '0' THEN '' ELSE '1' END,
    `0717` = CASE WHEN `0717` IS NULL OR '0' THEN '' ELSE '1' END,
    `0724` = CASE WHEN `0724` IS NULL OR '0' THEN '' ELSE '1' END,
    `0731` = CASE WHEN `0731` IS NULL OR '0' THEN '' ELSE '1' END,
    `0807` = CASE WHEN `0807` IS NULL OR '0' THEN '' ELSE '1' END,
    `0808` = CASE WHEN `0808` IS NULL OR '0' THEN '' ELSE '1' END,
    `0814` = CASE WHEN `0814` IS NULL OR '0' THEN '' ELSE '1' END,
    `0821` = CASE WHEN `0821` IS NULL OR '0' THEN '' ELSE '1' END,
    `0828` = CASE WHEN `0828` IS NULL OR '0' THEN '' ELSE '1' END,
    `0902` = CASE WHEN `0902` IS NULL OR '0' THEN '' ELSE '1' END,
    `0904` = CASE WHEN `0904` IS NULL OR '0' THEN '' ELSE '1' END,
    `0911` = CASE WHEN `0911` IS NULL OR '0' THEN '' ELSE '1' END,
    `0918` = CASE WHEN `0918` IS NULL OR '0' THEN '' ELSE '1' END,
    `0925` = CASE WHEN `0925` IS NULL OR '0' THEN '' ELSE '1' END,
    `1002` = CASE WHEN `1002` IS NULL OR '0' THEN '' ELSE '1' END,
    `1009` = CASE WHEN `1009` IS NULL OR '0' THEN '' ELSE '1' END,
    `1016` = CASE WHEN `1016` IS NULL OR '0' THEN '' ELSE '1' END,
    `1023` = CASE WHEN `1023` IS NULL OR '0' THEN '' ELSE '1' END,
    `1030` = CASE WHEN `1030` IS NULL OR '0' THEN '' ELSE '1' END,
    `1106` = CASE WHEN `1106` IS NULL OR '0' THEN '' ELSE '1' END,
    `1120` = CASE WHEN `1120` IS NULL OR '0' THEN '' ELSE '1' END,
    `1120b` = CASE WHEN `1120b` IS NULL OR '0' THEN '' ELSE '1' END,
    `1127` = CASE WHEN `1127` IS NULL OR '0' THEN '' ELSE '1' END,
    `1130` = CASE WHEN `1130` IS NULL OR '0' THEN '' ELSE '1' END,
    `1130b` = CASE WHEN `1130b` IS NULL OR '0' THEN '' ELSE '1' END,
    `1204` = CASE WHEN `1204` IS NULL OR '0' THEN '' ELSE '1' END,
    `1211` = CASE WHEN `1211` IS NULL OR '0' THEN '' ELSE '1' END,
    `1218` = CASE WHEN `1218` IS NULL OR '0' THEN '' ELSE '1' END,
    `1218b` = CASE WHEN `1218b` IS NULL OR '0' THEN '' ELSE '1' END,
    `1218c` = CASE WHEN `1218c` IS NULL OR '0' THEN '' ELSE '1' END,
    `1218d` = CASE WHEN `1218d` IS NULL OR '0' THEN '' ELSE '1' END,
    `1219` = CASE WHEN `1219` IS NULL OR '0' THEN '' ELSE '1' END,
    `1219b` = CASE WHEN `1219b` IS NULL OR '0' THEN '' ELSE '1' END,
    `1219c` = CASE WHEN `1219c` IS NULL OR '0' THEN '' ELSE '1' END,
    `1219d` = CASE WHEN `1219d` IS NULL OR '0' THEN '' ELSE '1' END,
    `1219e` = CASE WHEN `1219e` IS NULL OR '0' THEN '' ELSE '1' END
    WHERE empno='0005312004';
    SEMrush
    this was just one of the 5000 rows i need to update.

    How can i use loop on this without getting server gone away error.

    im trying to update 5000 rows... so basically im using a large loop here.

    is there any better way in order to avoid server issue?

    Any help would be appreciated.

    Thank you
     
    neilfurry, Dec 21, 2017 IP
    SEMrush
  2. Benanamen

    Benanamen Peon

    Messages:
    19
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    3
    #2
    Is this a one time update? If so, the easiest way to do this is with ETL Software.
    https://en.wikipedia.org/wiki/Extract,_transform,_load
     
    Benanamen, Jan 3, 2018 IP
  3. Gihan

    Gihan Member

    Messages:
    36
    Likes Received:
    1
    Best Answers:
    0
    Trophy Points:
    38
    #3
    what is the language and extesion
    PHP , MySQLi & PDO i can make it and give
     
    Gihan, Jan 6, 2018 IP
  4. neilfurry

    neilfurry Member

    Messages:
    46
    Likes Received:
    1
    Best Answers:
    0
    Trophy Points:
    38
    #4
    Yes, im using
    PHP , MySQLi & PDO
     
    neilfurry, Jan 6, 2018 IP
  5. Benanamen

    Benanamen Peon

    Messages:
    19
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    3
    #5
    Your using Mysqli AND PDO in the same script? You don't use both. Use PDO and be done with it.
     
    Benanamen, Jan 7, 2018 IP
  6. neilfurry

    neilfurry Member

    Messages:
    46
    Likes Received:
    1
    Best Answers:
    0
    Trophy Points:
    38
    #6
    PHP and Mysqli
     
    neilfurry, Jan 7, 2018 IP
  7. Benanamen

    Benanamen Peon

    Messages:
    19
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    3
    #7
    You still didn't answer if this is a one time update you need to do. Where is the data coming from in the first place?
     
    Benanamen, Jan 7, 2018 IP
  8. neilfurry

    neilfurry Member

    Messages:
    46
    Likes Received:
    1
    Best Answers:
    0
    Trophy Points:
    38
    #8
    this is a one time update, and data comes from excel file.,
     
    neilfurry, Jan 7, 2018 IP
  9. Benanamen

    Benanamen Peon

    Messages:
    19
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    3
    #9
    Did you look at ETL software as suggested? This is a good situation for it. If you are able to provide the file I can guide you.
     
    Benanamen, Jan 7, 2018 IP