Author |
Topic |
vinylpimp
Starting Member
16 Posts |
Posted - 2012-12-20 : 19:25:30
|
Hi allI am very new to SQL queries, I have this as my first question here.Currently I have a table columns NUMBER, DATESo we have1 01/01/20122 01/02/2012 3 01/03/2012etc...I know how to update multiple lines to a single new date, but I want to make a SINGLE update many numbers to various dates, such as1 02/01/2012 2 03/02/20123 02/03/2012Thanks in advance! |
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
vinylpimp
Starting Member
16 Posts |
Posted - 2012-12-20 : 19:42:37
|
The example is that we have a table of two columns "Number" "Date"NUMBER , DATE1 , 01/01/20122 , 01/02/20123 , 01/03/2012Now I want to update multiple lines to different dates in one query. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
vinylpimp
Starting Member
16 Posts |
Posted - 2012-12-20 : 19:51:54
|
Different Dates = Giving new data for column "Date"So I want the whole table from1 , 01/01/20122 , 01/02/20123 , 01/03/2012 to become1 , 02/01/20122 , 03/02/20123 , 02/03/2012with a single query. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
vinylpimp
Starting Member
16 Posts |
Posted - 2012-12-20 : 19:56:19
|
I see, I want to totally remove the original data and input entirely new data, because there is no logic about the changes I will make to the database.I just have a list of numbers with a list of dates I want to replace. |
|
|
vinylpimp
Starting Member
16 Posts |
Posted - 2012-12-20 : 20:04:38
|
My new data will have the "NUMBER" field next to date, so I am hoping to achieve the following in a simple queryIf Nubmber ='1' then Date ='02/01/2012'If Nubmber ='2' then Date ='03/02/2012' |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2012-12-20 : 20:46:51
|
Then is this oneDATEADD(mm,Number,Date)ORCase When Number = 1 then DATEADD(mm,Number,Date) End..... |
|
|
vinylpimp
Starting Member
16 Posts |
Posted - 2012-12-21 : 06:38:43
|
Thanks!!! ^^ |
|
|
vinylpimp
Starting Member
16 Posts |
Posted - 2012-12-27 : 13:45:53
|
quote: Originally posted by sodeep Then is this oneDATEADD(mm,Number,Date)ORCase When Number = 1 then DATEADD(mm,Number,Date) End.....
This actually still restricted me to add days to the original data, what if I want to change the date but have no pattern of the changes? Some can be add, and some will be minus. Better if I can just replace it with another date altogether. |
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2012-12-27 : 14:04:40
|
Do you just want to make random changes to dates? You still haven't told us what changes you want to make or the logic to make those changes. How do you know which shuold be plus and which should be minus?JimEveryday I learn something that somebody else already knew |
|
|
vinylpimp
Starting Member
16 Posts |
Posted - 2012-12-27 : 14:38:23
|
Here is my tableListingID, SaleDateI have all the affected rows with ListingID and the correct SaleDate on a excel sheet ready to use to replace these incorrect dates you see in the photo.Lets sayListingID '25640' needs to be 2012-08-01 00:00:00:0000ListingID '69348' needs to be 2012-08-04 00:00:00:0000 |
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2012-12-27 : 14:46:06
|
UPDATE yourTableSET SalesDate = CASE WHEN listingID = '25640' THEN '2012-08-01 00:00:00:0000' WHEN listingID = '69348' THEN '2012-08-04 00:00:00:0000' .... ENDIf possible, import the Excel to SQL Server, then you can do this with a simple join, instead of listing out each one.JimEveryday I learn something that somebody else already knew |
|
|
vinylpimp
Starting Member
16 Posts |
Posted - 2012-12-27 : 14:59:30
|
Nice one Jim! |
|
|
vinylpimp
Starting Member
16 Posts |
Posted - 2012-12-29 : 15:16:52
|
I am getting "Cannot insert the value NULL into column 'SaleDate', table 'VinylPimp.dbo.DiscogsSale'; column does not allow nulls. UPDATE fails.The statement has been terminated."I usedupdate [VinylPimp].[dbo].[discogssale]SET SaleDate = CASE WHEN listingID = '66474' THEN CAST('2012-10-31 00:00:00.000' AS DATETIME)endThis is my column's property |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2012-12-29 : 15:39:08
|
Maybe this:UPDATE yourTableSET SalesDate = CASE WHEN listingID = '25640' THEN '2012-08-01 00:00:00:0000' WHEN listingID = '69348' THEN '2012-08-04 00:00:00:0000' ELSE SalesDate END |
|
|
vinylpimp
Starting Member
16 Posts |
Posted - 2012-12-29 : 15:58:49
|
That did the trick, thank you so much, all the dates are now correct! |
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2012-12-30 : 10:42:31
|
For one time tasks like this - I would have created a new column in the Excel spreadsheet as a formula to create the following statement:="UPDATE yourTable SET SalesDate = 'NewDate' WHERE listingID = 'listingID';"In the above, we would replace 'NewDate' and 'listingID' with cell references (e.g. 'A1'). Once you have the formula created, copy the formula to every cell for all rows you want to update. This will generate a column with all of the update statements you need. Then, just copy all of those from the Excel spreadsheet into a query window and execute. |
|
|
vinylpimp
Starting Member
16 Posts |
Posted - 2012-12-30 : 11:10:28
|
quote: Originally posted by jeffw8713 In the above, we would replace 'NewDate' and 'listingID' with cell references (e.g. 'A1'). Once you have the formula created, copy the formula to every cell for all rows you want to update. This will generate a column with all of the update statements you need. Then, just copy all of those from the Excel spreadsheet into a query window and execute.
Exactly what I did yesterday, this forum is great. |
|
|
|