Select and update statement

  • Kindly assist on the below query

    1. Convert all old data (without _N) to _OLD. Example, 000015 to 000015_OLD, same goes for the EmpName table, FURUSAKI to FURUSAKI_OLD

    while excluding / ignoring certain character such as '85XXXX', '80XXXX', 'TRXXXX', 'CRXXXX', 'MY', etc

    Basically, I wan amend the data with '00XXX' in front only.

    2. Convert all new data (with _N) to without  '_N'. Example, 000001_N to 000001

  • Try these 2 queries

    UPDATE EmpNameTable
    SET Empname = Empname + '_OLD'
    WHERE Empname NOT LIKE '%[_]OLD' -- Do not update when already updated
    AND Empname NOT LIKE '85%' -- when starts with 85
    AND Empname NOT LIKE '80%' -- when starts with 80
    AND Empname NOT LIKE 'TR%' -- when starts with TR
    UPDATE EmpNameTable
    SET Empname = LEFT(Empname, LEN(Empname) -2)
    WHERE Empname LIKE '%[_]N' -- Ends with _N


  • Good day sir,

    Thank you very much for the support.

    May I know with the above query, is it possible to update 3 table at the same time as the above is only apply to "EmpName".

    I would like to update 3 table at the same time, "EmpID", "EmpName" & "CardNo"


  • Do you mean three columns at the same time?  Yes, you can update as many as you like - provided (I think) they're all in the same table.


  • Good day John,

    Is it possible if you provide the query using my case as the sample for better understanding?

  • No.  I'm not here to do your (home)work for you.  I've given you a link to get you going, and Des has also provided you with a query.  Use both of those and see whether you can come up with something that works for you.  If you struggle, please post what you've tried and we'll try to help.


  • Show us what you have tried, and what you are trying to achieve.

    Then we can assist you to correct any errors in the code.

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply