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 TRUPDATE 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.
John
-
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.
John
-
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 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply