| Author |
 Topic  |
|
|
vinylpimp
Starting Member
16 Posts |
 Posted - 12/20/2012 : 19:25:30
|
Hi all
I am very new to SQL queries, I have this as my first question here.
Currently I have a table columns NUMBER, DATE
So we have
1 01/01/2012
2 01/02/2012
3 01/03/2012
etc...
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 as
1 02/01/2012
2 03/02/2012
3 02/03/2012
Thanks in advance! |
Edited by - vinylpimp on 12/20/2012 19:26:35
|
|
|
tkizer
Almighty SQL Goddess
USA
35007 Posts |
|
|
vinylpimp
Starting Member
16 Posts |
Posted - 12/20/2012 : 19:42:37
|
The example is that we have a table of two columns "Number" "Date"
NUMBER , DATE
1 , 01/01/2012
2 , 01/02/2012
3 , 01/03/2012
Now I want to update multiple lines to different dates in one query. |
 |
|
|
tkizer
Almighty SQL Goddess
USA
35007 Posts |
|
|
vinylpimp
Starting Member
16 Posts |
Posted - 12/20/2012 : 19:51:54
|
Different Dates = Giving new data for column "Date"
So I want the whole table from
1 , 01/01/2012
2 , 01/02/2012
3 , 01/03/2012
to become
1 , 02/01/2012
2 , 03/02/2012
3 , 02/03/2012
with a single query. |
Edited by - vinylpimp on 12/20/2012 19:52:27 |
|
|
|
tkizer
Almighty SQL Goddess
USA
35007 Posts |
|
|
vinylpimp
Starting Member
16 Posts |
Posted - 12/20/2012 : 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. |
Edited by - vinylpimp on 12/20/2012 19:58:04 |
|
|
|
vinylpimp
Starting Member
16 Posts |
Posted - 12/20/2012 : 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 query
If Nubmber ='1' then Date ='02/01/2012'
If Nubmber ='2' then Date ='03/02/2012'
|
|
|
|
sodeep
Flowing Fount of Yak Knowledge
USA
7173 Posts |
Posted - 12/20/2012 : 20:46:51
|
Then is this one
DATEADD(mm,Number,Date)
OR
Case When Number = 1 then DATEADD(mm,Number,Date) End
..... |
|
|
|
vinylpimp
Starting Member
16 Posts |
Posted - 12/21/2012 : 06:38:43
|
| Thanks!!! ^^ |
|
|
|
vinylpimp
Starting Member
16 Posts |
Posted - 12/27/2012 : 13:45:53
|
quote:
Originally posted by sodeep
Then is this one
DATEADD(mm,Number,Date)
OR
Case 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
Flowing Fount of Yak Knowledge
USA
2868 Posts |
Posted - 12/27/2012 : 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?
Jim
Everyday I learn something that somebody else already knew |
|
|
|
vinylpimp
Starting Member
16 Posts |
Posted - 12/27/2012 : 14:38:23
|
Here is my table
ListingID, SaleDate
I 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 say
ListingID '25640' needs to be 2012-08-01 00:00:00:0000
ListingID '69348' needs to be 2012-08-04 00:00:00:0000 |
Edited by - vinylpimp on 12/27/2012 14:41:37 |
|
|
|
jimf
Flowing Fount of Yak Knowledge
USA
2868 Posts |
Posted - 12/27/2012 : 14:46:06
|
UPDATE yourTable
SET SalesDate = CASE WHEN listingID = '25640' THEN '2012-08-01 00:00:00:0000'
WHEN listingID = '69348' THEN '2012-08-04 00:00:00:0000'
....
END
If possible, import the Excel to SQL Server, then you can do this with a simple join, instead of listing out each one.
Jim
Everyday I learn something that somebody else already knew |
|
|
|
vinylpimp
Starting Member
16 Posts |
Posted - 12/27/2012 : 14:59:30
|
| Nice one Jim! |
|
|
|
vinylpimp
Starting Member
16 Posts |
Posted - 12/29/2012 : 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 used
update [VinylPimp].[dbo].[discogssale]
SET SaleDate = CASE
WHEN listingID = '66474' THEN CAST('2012-10-31 00:00:00.000' AS DATETIME)
end
This is my column's property
 |
|
|
|
sodeep
Flowing Fount of Yak Knowledge
USA
7173 Posts |
Posted - 12/29/2012 : 15:39:08
|
Maybe this:
UPDATE yourTable
SET 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 - 12/29/2012 : 15:58:49
|
| That did the trick, thank you so much, all the dates are now correct! |
|
|
|
jeffw8713
Aged Yak Warrior
USA
696 Posts |
Posted - 12/30/2012 : 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 - 12/30/2012 : 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. |
|
|
| |
Topic |
|
How to update part of a table
