Select and update statement

  • bb004938

    Valued Member

    Points: 64

    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

  • DesNorton

    SSC-Insane

    Points: 22812

    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

     

  • bb004938

    Valued Member

    Points: 64

    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"

     

  • John Mitchell-245523

    SSC Guru

    Points: 148360

    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.

    https://docs.microsoft.com/en-us/sql/t-sql/queries/update-transact-sql?view=sql-server-ver15#UpdateExamples

    John

  • bb004938

    Valued Member

    Points: 64

    Good day John,

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

  • John Mitchell-245523

    SSC Guru

    Points: 148360

    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.

    John

  • DesNorton

    SSC-Insane

    Points: 22812

    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