Quick help with a simple SQL Update Statement
I'm in a bit of a hurry to update some data...I want to make sure I get it right the first time as this is production data.
Here's the structure:
I have a field called 'PostDate' that has wrong data in it and I need to fix it. The correct value exists in another table. And yes, I do indeed have a foreign key to link the two tables together (let's call the two tables "myTransactions" table and "myFiles" table). The myFile table has 'FileIDnum' as its primary key. The myTransactions table has 'BatchID' as the foreign key that links back to 'FileIDnum' ('PostDate' is found in 'myTransactions').
Now here is the goal:
The myFile table has a field called 'FileName' which is a varchar. The first 8 characters are of the format: YYYYMMDD (ie. 20081205FileInfo.txt). I know that SQL Server will accept in a SQL statement a date spelled out like: 'YYYYMMDD' if you use the single quotes. The string is automatically converted into date/time. I want to take advantage of this by getting the first 8 characters out of this string in the 'FileName' field and include that in my update statement to update the PostDate field of the myTransactions table (note: 'PostDate' is dataTyped as date/time).
Please help...
Here's the structure:
I have a field called 'PostDate' that has wrong data in it and I need to fix it. The correct value exists in another table. And yes, I do indeed have a foreign key to link the two tables together (let's call the two tables "myTransactions" table and "myFiles" table). The myFile table has 'FileIDnum' as its primary key. The myTransactions table has 'BatchID' as the foreign key that links back to 'FileIDnum' ('PostDate' is found in 'myTransactions').
Now here is the goal:
The myFile table has a field called 'FileName' which is a varchar. The first 8 characters are of the format: YYYYMMDD (ie. 20081205FileInfo.txt). I know that SQL Server will accept in a SQL statement a date spelled out like: 'YYYYMMDD' if you use the single quotes. The string is automatically converted into date/time. I want to take advantage of this by getting the first 8 characters out of this string in the 'FileName' field and include that in my update statement to update the PostDate field of the myTransactions table (note: 'PostDate' is dataTyped as date/time).
Please help...
Last Comment
ASKER
Did you test this....I really need it to be perfect.
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
I havent' created the tables and tested this, but you could see how this would run in a test case
Update myTransactions
set PostDate = Substring(myFiles.FileName
From myTransactions a
join myFiles on myFiles.FileIDNum = a.BatchID
Update myTransactions
set PostDate = Substring(myFiles.FileName
From myTransactions a
join myFiles on myFiles.FileIDNum = a.BatchID
I created and tested this with tables named like yours
Update myTransactions
set PostDate = Substring(myFiles.FileName
From myTransactions a
join myFiles on myFiles.FileIDNum = a.BatchID
Update myTransactions
set PostDate = Substring(myFiles.FileName
From myTransactions a
join myFiles on myFiles.FileIDNum = a.BatchID
ASKER
Won't I want to comment out the rollback line so I can see the results? I could still use the rollback if I need to at that point.
No, good point, but I got around that by SELECTing the UPDATEd rows both before and *after* the UPDATE, but prior to issuing the ROLLBACK.
Honestly, this code is so simple I'm 99% sure it will work, but for a prod system, you should ALWAYS test anyway, of course.
ASKER
Thank you so much...that did it!!
Microsoft SQL Server
Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.
171K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
SET PostDate = LEFT(myFiles.Filename, 8)
FROM myTransactions
INNER JOIN myFiles ON myFiles.FileIDnum = myTransactions.BatchID