By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,506 Members | 1,881 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,506 IT Pros & Developers. It's quick & easy.

Merge SQL Statement Will Not Compile

P: 2
Merge SQL statement will not compile. Can anyone help me out?

Expand|Select|Wrap|Line Numbers
  1.  Create Or Replace Procedure P_pvt_merge 
  2. As
  3. Begin 
  4. Merge Into Employees2 B
  5. Using (
  6. Select Employee_id, First_name, Last_name, Dept_no, Salary
  7. From Employees
  8. Where Dept_no = 20) E
  9. On (b.employee_id = E.employee_id)
  10. When Matched Then
  11. Update Set B.employee_id = E.employee_id,
  12. B.first_name = E.first_name,
  13. B.last_name = E.last_name,
  14. B.dept_no = E.dept_no,
  15. B.salary = E.salary 
  16. When Not Matched Then
  17. Insert (b.employee_id, B.first_name, B.last_name, B.dept_no, B.salary)
  18. Values (e.employee_id, E.first_name, E.last_name, E.dept_no, E.salary)
  19. Commit;
  20. End;
  21.  
/
Jan 4 '06 #1
Share this Question
Share on Google+
1 Reply


P: 1
Merge SQL statement will not compile. Can anyone help me out?

Expand|Select|Wrap|Line Numbers
  1.  Create Or Replace Procedure P_pvt_merge 
  2. As
  3. Begin 
  4. Merge Into Employees2 B
  5. Using (
  6. Select Employee_id, First_name, Last_name, Dept_no, Salary
  7. From Employees
  8. Where Dept_no = 20) E
  9. On (b.employee_id = E.employee_id)
  10. When Matched Then
  11. Update Set B.employee_id = E.employee_id,
  12. B.first_name = E.first_name,
  13. B.last_name = E.last_name,
  14. B.dept_no = E.dept_no,
  15. B.salary = E.salary 
  16. When Not Matched Then
  17. Insert (b.employee_id, B.first_name, B.last_name, B.dept_no, B.salary)
  18. Values (e.employee_id, E.first_name, E.last_name, E.dept_no, E.salary)
  19. Commit;
  20. End;
  21.  
/

Answer:
In merge statement u can not use thos columns in UPDATE SET statement which are specified in ON condition .....

here,u might be getting error like : Invalid Identifier Employee_id .... Rite?
so u just remove the line B.employee_id = E.employee_id from UPDATE SET
statement...
Feb 21 '06 #2

Post your reply

Sign in to post your reply or Sign up for a free account.