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
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Update query will not run?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

wafw1971
Yak Posting Veteran

75 Posts

Posted - 02/20/2013 :  03:59:22  Show Profile  Reply with Quote
When I run the query (1st Code) below I get 1.37 million random Departure Dates based on the current Arrival Date in the database, this is good news. However when I try to update the database with the 2nd Code query I get an error message(See below) and I don't know why. Can you help?

Msg 116, Level 16, State 1, Line 5 Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

1st Code
SELECT ArrivalDate, DATEADD(day, 1 + RAND(checksum(NEWID()))
* LengthOfStay.LengthofStay, ArrivalDate) AS DepartureDate
FROM Bookings, LengthOfStay
ORDER BY ArrivalDate

2nd Code
USE Occupancy
Update Bookings
Set DepartureDate = (SELECT ArrivalDate, DATEADD(day, 1 + RAND(checksum(NEWID()))*1.5
* LengthOfStay.LengthofStay, ArrivalDate))
FROM LengthOfStay, Bookings

Thanks

Wayne

bandi
Flowing Fount of Yak Knowledge

India
2217 Posts

Posted - 02/20/2013 :  04:08:03  Show Profile  Reply with Quote
Try this....
Update b
Set b.DepartureDate = DATEADD(day, 1 + RAND(checksum(NEWID()))*1.5*l.LengthofStay, b.ArrivalDate)
FROM Bookings b, LengthOfStay l

--
Chandu
Go to Top of Page

wafw1971
Yak Posting Veteran

75 Posts

Posted - 02/20/2013 :  04:10:35  Show Profile  Reply with Quote
Hi Chandu

I am new to SQL what are the single b's and l's mean?

Thanks

Wayne
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2217 Posts

Posted - 02/20/2013 :  04:15:09  Show Profile  Reply with Quote
quote:
Originally posted by wafw1971

Hi Chandu
I am new to SQL what are the single b's and l's mean?
Thanks
Wayne

b, l are the alias names for corresponding tables..
Are you updating Booking table based on which criteria? I mean is there any common column between those two tables........

--
Chandu

Edited by - bandi on 02/20/2013 04:15:26
Go to Top of Page

wafw1971
Yak Posting Veteran

75 Posts

Posted - 02/20/2013 :  04:19:59  Show Profile  Reply with Quote
Hi Chandu

All sorted thanks for you help.

Wayne
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2217 Posts

Posted - 02/20/2013 :  04:24:37  Show Profile  Reply with Quote
quote:
Originally posted by wafw1971

Hi Chandu
All sorted thanks for you help.
Wayne


Welcome
Refer this link to know more about UPDATE statement
http://sqlusa.com/articles2005/sqlupdate/
--
Chandu

Edited by - bandi on 02/20/2013 04:24:54
Go to Top of Page

wafw1971
Yak Posting Veteran

75 Posts

Posted - 02/20/2013 :  04:33:20  Show Profile  Reply with Quote
Hello again Chandu, it seems the randomising has changed, before on my select query I would get the departure randomising between 1 and 28 days the query below has only used 1 and 2 days. USE Occupancy Update B Set DepartureDate = DATEADD(day, 1 + RAND(checksum(NEWID()))*1.5 * L.LengthofStay, B.ArrivalDate) FROM LengthOfStay L, Bookings B
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2217 Posts

Posted - 02/20/2013 :  04:45:21  Show Profile  Reply with Quote
Can you post structure of those two tables and also sample data for tables...

just execute this one.. you can add upto 28 days to the current date randomly....
SELECT DATEADD(DD, 1+RAND()*28, GETDATE())
GO 10;

--
Chandu

Edited by - bandi on 02/20/2013 05:00:22
Go to Top of Page

wafw1971
Yak Posting Veteran

75 Posts

Posted - 02/20/2013 :  04:57:19  Show Profile  Reply with Quote
The Length of Stay table is numbered between 1 and 28, and the

Booking_Skey BookingNumber ArrivalDate DepartureDate BookingDate CancelledDate BookingValue PitchType_Skey Site_Skey
1313258 NULL 02/01/2010 NULL NULL NULL NULL 3 2
1313259 NULL 02/01/2010 NULL NULL NULL NULL 3 2
1313260 NULL 02/01/2010 NULL NULL NULL NULL 3 2
1313261 NULL 02/01/2010 NULL NULL NULL NULL 3 2
1313262 NULL 02/01/2010 NULL NULL NULL NULL 3 2
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2217 Posts

Posted - 02/20/2013 :  05:19:07  Show Profile  Reply with Quote
Just execute this script...

DECLARE @bookings TABLE(Booking_Skey INT, BookingNumber INT, ArrivalDate DATE, DepartureDate DATE,BookingDate DATE,CancelledDate DATE,BookingValue INT, PitchType_Skey INT,Site_Skey INT)
insert into @bookings
SELECT 1313258, NULL, '02/01/2010', NULL, NULL, NULL, NULL, 3, 2 union all
SELECT 1313259, NULL, '02/01/2010', NULL, NULL, NULL, NULL, 3, 2 union all
SELECT 1313260, NULL, '02/01/2010', NULL, NULL, NULL, NULL, 3, 2 union all
SELECT 1313261, NULL, '02/01/2010', NULL, NULL, NULL, NULL, 3, 2 union all
SELECT 1313262, NULL, '02/01/2010', NULL, NULL, NULL, NULL, 3, 2
DECLARE @LengthOfStay TABLE( LengthofStay INT) 
INSERT INTO @LengthOfStay VALUES(28),(3), (4), (5),(6),(7),(10),(12),(14),(20),(21),(8),(9),(27)

SELECT ArrivalDate, DATEADD(day, 1 + RAND(checksum(NEWID()))
* L.LengthofStay, ArrivalDate) AS DepartureDate 
FROM @bookings, @LengthOfStay l
ORDER BY ArrivalDate

Update b
Set b.DepartureDate = DATEADD(day, 1 + RAND(checksum(NEWID()))*l.LengthofStay, b.ArrivalDate)
FROM @Bookings b, @LengthOfStay l
SELECT * FROM @bookings


Simply if you want to randomise departureDate upto 28 days then no need of join also...
Update bookings
Set DepartureDate = DATEADD(day, 1 + RAND(CHECKSUM(NEWID()))*28, ArrivalDate)
SELECT * FROM bookings


--
Chandu
Go to Top of Page

wafw1971
Yak Posting Veteran

75 Posts

Posted - 02/20/2013 :  05:33:49  Show Profile  Reply with Quote
Thank you.
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2217 Posts

Posted - 02/20/2013 :  05:40:14  Show Profile  Reply with Quote
quote:
Originally posted by wafw1971

Thank you.


Did you get my point? Is it working...?


--
Chandu
Go to Top of Page

wafw1971
Yak Posting Veteran

75 Posts

Posted - 02/20/2013 :  05:54:13  Show Profile  Reply with Quote
Hi Chandu

I used the query below, it was the simplest thing to do.

Update bookings
Set DepartureDate = DATEADD(day, 1 + RAND(CHECKSUM(NEWID()))*28, ArrivalDate)
SELECT * FROM bookings

Thanks again

Wayne
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2217 Posts

Posted - 02/20/2013 :  06:12:30  Show Profile  Reply with Quote
Welcome

--
Chandu
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