Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 How to update part of a table

Author  Topic 

vinylpimp
Starting Member

16 Posts

Posted - 2012-12-20 : 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!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-12-20 : 19:30:26
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 - 2012-12-20 : 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.
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-12-20 : 19:48:59
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
Go to Top of Page

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

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-12-20 : 19:55:02
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 - 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.
Go to Top of Page

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 query

If Nubmber ='1' then Date ='02/01/2012'
If Nubmber ='2' then Date ='03/02/2012'


Go to Top of Page

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2012-12-20 : 20:46:51
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 - 2012-12-21 : 06:38:43
Thanks!!! ^^
Go to Top of Page

vinylpimp
Starting Member

16 Posts

Posted - 2012-12-27 : 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.
Go to Top of Page

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?

Jim

Everyday I learn something that somebody else already knew
Go to Top of Page

vinylpimp
Starting Member

16 Posts

Posted - 2012-12-27 : 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
Go to Top of Page

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-12-27 : 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
Go to Top of Page

vinylpimp
Starting Member

16 Posts

Posted - 2012-12-27 : 14:59:30
Nice one Jim!
Go to Top of Page

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 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
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2012-12-29 : 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
Go to Top of Page

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

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.



Go to Top of Page

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

- Advertisement -