INSERT.... SELECT

[Anon]

Should this work?

INSERT INTO HOTELONLINE SET PREFERENCEID=366, HOTELID=D.HOTELID, ROOMID=D.ROOMID, BOOKDATE=D.BOOKDATE, ROOMTYPE=D1.ROOMTYPE
SELECT D.HOTELID, D.ROOMID, D.BOOKDATE, D1.ROOMTYPE
FROM ROOMAVAIL AS D, HOTELROOM AS D1, HOTEL AS D2
WHERE
(D.ROOMID = D1.ROOMID) AND (D.HOTELID=D1.HOTELID) AND (D.HOTELID=D2.HOTELID)

(it doesn't you see!)

Help!

Thanks

[cscosby]

Try this instead.

INSERT INTO HOTELONLINE
VALUES (PREFERENCEID, HOTELID, ROOMID, BOOKDATE, ROOMTYPE)
SELECT 366, D.HOTELID, D.ROOMID, D.BOOKDATE,D1.ROOMTYPE
FROM ROOMAVAIL AS D, HOTELROOM AS D1, HOTEL AS D2
WHERE (D.ROOMID = D1.ROOMID) AND (D.HOTELID = D1.HOTELID) AND ( D.HOTELID = D2.HOTELID)

[[deleted]]

No it shouldn't.

You can't specify a few columns and expect SQL to know which other columns you want the data from the SELECT to go into.

Either specify everything or specify nothing.

[Anon]

Thanks - it didn't quite work but got me on the track to:

INSERT INTO HOTELONLINE SELECT 371, D.HOTELID, D.ROOMID, D1.ROOMTYPE, D.BOOKDATE FROM ROOMAVAIL AS D, HOTELROOM AS D1, HOTEL AS D2 WHERE (D.ROOMID = D1.ROOMID) AND (D.HOTELID=D1.HOTELID) AND (D.HOTELID=D2.HOTELID)

since I'm inserting into all the fields that's fine.

I guess it's

INSERT INTO HOTELONLINE (PREFERENCEID,HOTELID,ROOMID,ROOMTYPE,BOOKDATE) SELECT 371, D.HOTELID, D.ROOMID, D1.ROOMTYPE, D.BOOKDATE FROM ROOMAVAIL AS D, HOTELROOM AS D1, HOTEL AS D2 WHERE (D.ROOMID = D1.ROOMID) AND (D.HOTELID=D1.HOTELID) AND (D.HOTELID=D2.HOTELID)

if I only wanted to insert into some of the fields? (I got that from the next post on INSERT ... SELECT).

thanks again!

Elizabeth