| Author |
 Topic  |
|
|
wafw1971
Yak Posting Veteran
68 Posts |
 Posted - 02/20/2013 : 03:59:22
|
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
1390 Posts |
Posted - 02/20/2013 : 04:08:03
|
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 |
 |
|
|
wafw1971
Yak Posting Veteran
68 Posts |
Posted - 02/20/2013 : 04:10:35
|
Hi Chandu
I am new to SQL what are the single b's and l's mean?
Thanks
Wayne |
|
|
|
bandi
Flowing Fount of Yak Knowledge
India
1390 Posts |
Posted - 02/20/2013 : 04:15:09
|
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 |
|
|
|
wafw1971
Yak Posting Veteran
68 Posts |
Posted - 02/20/2013 : 04:19:59
|
Hi Chandu
All sorted thanks for you help.
Wayne |
|
|
|
bandi
Flowing Fount of Yak Knowledge
India
1390 Posts |
Posted - 02/20/2013 : 04:24:37
|
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 |
|
|
|
wafw1971
Yak Posting Veteran
68 Posts |
Posted - 02/20/2013 : 04:33:20
|
| 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 |
|
|
|
bandi
Flowing Fount of Yak Knowledge
India
1390 Posts |
Posted - 02/20/2013 : 04:45:21
|
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 |
|
|
|
wafw1971
Yak Posting Veteran
68 Posts |
Posted - 02/20/2013 : 04:57:19
|
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
|
|
|
|
bandi
Flowing Fount of Yak Knowledge
India
1390 Posts |
Posted - 02/20/2013 : 05:19:07
|
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 |
|
|
|
wafw1971
Yak Posting Veteran
68 Posts |
Posted - 02/20/2013 : 05:33:49
|
| Thank you. |
|
|
|
bandi
Flowing Fount of Yak Knowledge
India
1390 Posts |
Posted - 02/20/2013 : 05:40:14
|
quote:
Originally posted by wafw1971
Thank you.
Did you get my point? Is it working...?
--
Chandu |
|
|
|
wafw1971
Yak Posting Veteran
68 Posts |
Posted - 02/20/2013 : 05:54:13
|
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 |
|
|
|
bandi
Flowing Fount of Yak Knowledge
India
1390 Posts |
Posted - 02/20/2013 : 06:12:30
|
Welcome
--
Chandu |
|
|
| |
Topic |
|
|
|
Update query will not run?
