Update query will not run? - SQL Server Forums

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

SQL Server 2012 Forums

Transact-SQL (2012)

Update query will not run?

Author

Topic

wafw1971
Yak Posting Veteran

75 Posts

Posted - 2013-02-20 : 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
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-02-20 : 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

75 Posts

Posted - 2013-02-20 : 04:10:35

Hi Chandu

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

Thanks

Wayne

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-02-20 : 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

wafw1971
Yak Posting Veteran

75 Posts

Posted - 2013-02-20 : 04:19:59

Hi Chandu

All sorted thanks for you help.

Wayne

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-02-20 : 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

wafw1971
Yak Posting Veteran

75 Posts

Posted - 2013-02-20 : 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
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-02-20 : 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

wafw1971
Yak Posting Veteran

75 Posts

Posted - 2013-02-20 : 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
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-02-20 : 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

75 Posts

Posted - 2013-02-20 : 05:33:49

Thank you.

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-02-20 : 05:40:14

quote:
Originally posted by wafw1971

Thank you.

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

--
Chandu

wafw1971
Yak Posting Veteran

75 Posts

Posted - 2013-02-20 : 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
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-02-20 : 06:12:30

Welcome

--
Chandu

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources