SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 How to update part of a table
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

vinylpimp
Starting Member

16 Posts

Posted - 12/20/2012 :  19:25:30  Show Profile  Reply with Quote
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
37142 Posts

Posted - 12/20/2012 :  19:30:26  Show Profile  Visit tkizer's Homepage  Reply with Quote
What is the logic? Your sample data doesn't make sense...

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

vinylpimp
Starting Member

16 Posts

Posted - 12/20/2012 :  19:42:37  Show Profile  Reply with Quote
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.
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37142 Posts

Posted - 12/20/2012 :  19:48:59  Show Profile  Visit tkizer's Homepage  Reply with Quote
Yes, but what is the logic for "different dates"?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Edited by - tkizer on 12/20/2012 19:49:19
Go to Top of Page

vinylpimp
Starting Member

16 Posts

Posted - 12/20/2012 :  19:51:54  Show Profile  Reply with Quote
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
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37142 Posts

Posted - 12/20/2012 :  19:55:02  Show Profile  Visit tkizer's Homepage  Reply with Quote
You are missing my point. In order to put it into a single statement, there needs to be some logic that we can apply. For instance, are we adding 30 days to each date?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

vinylpimp
Starting Member

16 Posts

Posted - 12/20/2012 :  19:56:19  Show Profile  Reply with Quote
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
Go to Top of Page

vinylpimp
Starting Member

16 Posts

Posted - 12/20/2012 :  20:04:38  Show Profile  Reply with Quote
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'


Go to Top of Page

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 12/20/2012 :  20:46:51  Show Profile  Reply with Quote
Then is this one

DATEADD(mm,Number,Date)

OR

Case When Number = 1 then DATEADD(mm,Number,Date) End
.....
Go to Top of Page

vinylpimp
Starting Member

16 Posts

Posted - 12/21/2012 :  06:38:43  Show Profile  Reply with Quote
Thanks!!! ^^
Go to Top of Page

vinylpimp
Starting Member

16 Posts

Posted - 12/27/2012 :  13:45:53  Show Profile  Reply with Quote
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.
Go to Top of Page

jimf
Flowing Fount of Yak Knowledge

USA
2869 Posts

Posted - 12/27/2012 :  14:04:40  Show Profile  Reply with Quote
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
Go to Top of Page

vinylpimp
Starting Member

16 Posts

Posted - 12/27/2012 :  14:38:23  Show Profile  Reply with Quote


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
Go to Top of Page

jimf
Flowing Fount of Yak Knowledge

USA
2869 Posts

Posted - 12/27/2012 :  14:46:06  Show Profile  Reply with Quote
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
Go to Top of Page

vinylpimp
Starting Member

16 Posts

Posted - 12/27/2012 :  14:59:30  Show Profile  Reply with Quote
Nice one Jim!
Go to Top of Page

vinylpimp
Starting Member

16 Posts

Posted - 12/29/2012 :  15:16:52  Show Profile  Reply with Quote
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

Go to Top of Page

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 12/29/2012 :  15:39:08  Show Profile  Reply with Quote
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
Go to Top of Page

vinylpimp
Starting Member

16 Posts

Posted - 12/29/2012 :  15:58:49  Show Profile  Reply with Quote
That did the trick, thank you so much, all the dates are now correct!
Go to Top of Page

jeffw8713
Aged Yak Warrior

USA
798 Posts

Posted - 12/30/2012 :  10:42:31  Show Profile  Reply with Quote
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.



Go to Top of Page

vinylpimp
Starting Member

16 Posts

Posted - 12/30/2012 :  11:10:28  Show Profile  Reply with Quote
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000