0

I want to select from a table its values and then at the same SQL statement I want to insert these values into another table

SELECT  `DVD_ID`, `dvd_gerne`, `dvd_Director`, `dvd_price`, `dvd_Title`, `photoName` 
FROM `DVDs_Details` WHERE DVD_ID='1' 
AND INSERT INTO `sta177_Rented` VALUES([DVD_ID-1], [dvd_gerne-2], [dvd_Director-3], [dvd_price-4], [dvd_Title-5], [photoName-6]')
Dharman
  • 30,962
  • 25
  • 85
  • 135
Arif Muni
  • 19
  • 1
  • 5

3 Answers3

3

This is done with an Insert-With-Select

INSERT INTO `sta177_Rented`  (`DVD_ID`, `dvd_gerne`, `dvd_Director`, `dvd_price`, `dvd_Title`, `photoName`)
    SELECT  `DVD_ID`, `dvd_gerne`, `dvd_Director`, `dvd_price`, `dvd_Title`, `photoName` 
    FROM `DVDs_Details` 
    WHERE DVD_ID='1'
Community
  • 1
  • 1
Digital Chris
  • 6,177
  • 1
  • 20
  • 29
2

You can also specify which columns you want to insert values into like this

INSERT INTO `sta177_Rented` (`DVD_ID`, `dvd_gerne`, `dvd_Director`, `dvd_price`, `dvd_Title`, `photoName`)
SELECT  `DVD_ID`, `dvd_gerne`, `dvd_Director`, `dvd_price`, `dvd_Title`, `photoName`
FROM `DVDs_Details` WHERE DVD_ID='1'
migvill
  • 511
  • 3
  • 6
-1

You have to try something like below

Insert into sta177_Rented
SELECT  DVD_ID, dvd_gerne, dvd_Director, dvd_price, dvd_Title, photoName FROM 
DVDs_Details WHERE DVD_ID=1
Rao
  • 9
  • 1
  • 4
  • @KayNelson Thanks for your comments, but here we are trying to help each other to resolve the issues, pin pointing the syntax and English is not the right approach I think. – Rao Jan 17 '14 at 17:00
  • Hi Rao. When you post the wrong syntax, im supposed to point it out. Otherwise we have wrong answers. If you change your answer to a correct syntax, i will upvote it. – Mad Dog Tannen Jan 17 '14 at 17:48