| Author |
Topic |
|
GavinD1977
Yak Posting Veteran
83 Posts |
 Posted - 2007-03-21 : 06:56:38
|
This is an insert statement i'm trying to run. I want it to only insert this recird if the corresponding EMPLOY_REF exists in the EMPLOYEE table. Heres my statement:INSERT INTO SALHISTY(EMPLOY_REF, SALARY, SAL_REASON, SAL_DATE) VALUES ('8971','175000.0000','ANNRV','2007-04-01 00:00:00.000') WHERE '8971' IN (SELECT EMPLOY_REF FROM EMPLOYEE)This is the error message i'm getting.Server: Msg 156, Level 15, State 1, Line 3Incorrect syntax near the keyword 'WHERE'. Any help would be greatly appreciated. Thanks  |
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2007-03-21 : 07:00:49
|
| [code]if exists(SELECT * FROM EMPLOYEE where EMPLOY_REF = '8971')INSERT INTO SALHISTY(EMPLOY_REF, SALARY, SAL_REASON, SAL_DATE) VALUES ('8971','175000.0000','ANNRV','2007-04-01 00:00:00.000')[/code]Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
 |
|
|
GavinD1977
Yak Posting Veteran
83 Posts |
Posted - 2007-03-21 : 07:03:09
|
| OK, cool...............thanks very much Harsh.What was it thats wrong with the way i tried to go about it?Cheers, again :-) |
|
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2007-03-21 : 07:09:30
|
| It's in front of you!!Compare my solution with yours..the form of INSERT statement you are using does not allow WHERE clause to be used.Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-03-21 : 08:11:17
|
| [code]INSERT SALHISTY ( EMPLOY_REF, SALARY, SAL_REASON, SAL_DATE ) SELECT EMPLOY_REF, SALARY, SAL_REASON, SALDATEFROM EMPLOYEEWHERE EMPLOY_REF = '8971'[/code]Peter LarssonHelsingborg, Sweden |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
GavinD1977
Yak Posting Veteran
83 Posts |
Posted - 2007-03-21 : 09:59:35
|
| Guys i have to say thanks very much for the advice.........however I'd also like to take this opportunity to say i feel that some of the responses on here are very abrupt. The quote from X002548 above sort of highlights this. I am newbie (hence posting in a New To SQL forum) and i posted my question in a polite and friendly manner (also showing my code to highlight the fact i wasnt just asking questions.......but that I had tried to solve this myself first). The repsonse above doesnt help in the slightest! As does the reply by harsh stating "its in front of you". I am well aware that what I suggested was incorrect and that the reply posted solved my problem. All i was looking for then was someone to explain where and why i'd gone wrong.If your going to reply to newbie posts I would suggest you maybe take the lack of knowledge into account when replying. trying to learn SQL and getting abrupt posts back doesnt help in the slightest.Thanks. |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2007-03-21 : 10:12:26
|
| Do you expect us to provide solution along with the explaination of how it works?In contrast, we expect you to take some efforts, go through SQL help, google and try to understand the solution. Also, the solution I gave was not very difficult to understand. Besides, I gave you the reason why yours was failing.Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-03-21 : 10:22:47
|
| My suggestion, if you hven't noticed it yet, provides everything for you!You don't have to put in duplicate information, only the ID of the Employee.The best thing you can do to learn, is to compare our working suggestions with your own!Eventually you will see what you did wrong and learn from that.Peter LarssonHelsingborg, Sweden |
|
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2007-03-21 : 10:28:00
|
quote:
Originally posted by Peso
My suggestion, if you hven't noticed it yet, provides everything for you!You don't have to put in duplicate information, only the ID of the Employee.
Provided you have those same columns as a part of EMPLOYEE table and you read OP's mind correctly  Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
|
|
|
GavinD1977
Yak Posting Veteran
83 Posts |
Posted - 2007-03-21 : 10:32:53
|
quote:
Originally posted by harsh_athalye
In contrast, we expect you to take some efforts, go through SQL help, google and try to understand the solution. Also, the solution I gave was not very difficult to understand. Besides, I gave you the reason why yours was failing.Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED"
thats exactly what i did..and i posted my (attempted) code :-s |
|
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2007-03-21 : 10:40:22
|
| Gavin, I am not criticising you and it's good that you made attempt on your own before posting here. All I am saying is try to understand the solutions you get here. May be understanding it in parts will be more easy for you. After that, if you find some specific part difficult to understand, we can help you out.Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
|
|
|
GavinD1977
Yak Posting Veteran
83 Posts |
Posted - 2007-03-21 : 11:07:36
|
| OK, cheers harsh.And genuine thanks to all those who contributed to this. |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
|
Why doesnt this work............confused!?!?!