Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 6 of 6 total |
FROM clause in UPDATE statement |
Sat, Jan 15 2011 8:47 AM | Permanent Link |
Oliver METTRIX | Probably been asked a million times but why is FROM not accepted in an UPDATE statement. Seems that
UPDATE LIST_LINK L SET L.EMPLOYEEPIN = E.EMPLOYEEPIN FROM EMPLOYEE E, LIST_LINK L WHERE L.EMPLOYEEPIN= E.EMPLOYEEPIN; should be an easy way to update one field in a table with a field from another table. Thanks. Oliver |
Sat, Jan 15 2011 11:35 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Oliver
>Probably been asked a million times but why is FROM not accepted in an UPDATE statement. Seems that Probably something to do with the standard >UPDATE LIST_LINK L SET L.EMPLOYEEPIN = E.EMPLOYEEPIN FROM >EMPLOYEE E, LIST_LINK L WHERE L.EMPLOYEEPIN= E.EMPLOYEEPIN; > >should be an easy way to update one field in a table with a field from another table. It is but you have to use a sub select. eg from one of Tim's recent posts UPDATE InsertTest SET (ID,Description)=(SELECT ID,'Updated' FROM UpdateTest WHERE ID=InsertTest.ID) Roy Lambert [Team Elevate] |
Sat, Jan 15 2011 6:54 PM | Permanent Link |
Oliver METTRIX | Hello Roy, Well I tried it but it didn't work. Here's my actual SQL followed by the syntax error message: did I misunderstand your answer? UPDATE list_link SET (emplpin) = (SELECT employeePin FROM employee WHERE "FK-ID_employee" = employee.ID_employee) ; ElevateDB Error #700 An error was found in the statement at line 2 and column 51 (Expected NULL, SmallInt, Integer, BigInt, Float, Decimal, Interval Year, Interval Day, Interval Hour, Interval Minute, Interval Second, or Interval MSecond expression but instead found (SELECT ALL "employeePin" AS "employeePin" FROM "employee" WHERE "FK-ID_employee" = "employee"."ID_employee")) Roy Lambert wrote: Oliver >Probably been asked a million times but why is FROM not accepted in an UPDATE statement. Seems that Probably something to do with the standard >UPDATE LIST_LINK L SET L.EMPLOYEEPIN = E.EMPLOYEEPIN FROM >EMPLOYEE E, LIST_LINK L WHERE L.EMPLOYEEPIN= E.EMPLOYEEPIN; > >should be an easy way to update one field in a table with a field from another table. It is but you have to use a sub select. eg from one of Tim's recent posts UPDATE InsertTest SET (ID,Description)=(SELECT ID,'Updated' FROM UpdateTest WHERE ID=InsertTest.ID) Roy Lambert [Team Elevate] |
Sun, Jan 16 2011 5:16 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Oliver
>Well I tried it but it didn't work. Here's my actual SQL followed by the syntax error message: > >did I misunderstand your answer? It was actually Tim's answer and I think slightly. I just tried this UPDATE Contacts SET _Forename = (SELECT _Forename FROM Contacts WHERE _ID = 1000002) WHERE _ID = 1000003 on my database. Note I restricted the records to be updated. I don't know what your query SELECT employeePin FROM employee WHERE "FK-ID_employee" = employee.ID_employee returns. Try it and see. It may be that it returns several employeePin if so it won't work. If it returns just 1 that would replace every emplpin in the table. Roy Lambert [Team Elevate] |
Sun, Jan 16 2011 11:34 AM | Permanent Link |
Oliver METTRIX | Thanks Roy, I will give it a try.
Oliver Roy Lambert wrote: Oliver >Well I tried it but it didn't work. Here's my actual SQL followed by the syntax error message: > >did I misunderstand your answer? It was actually Tim's answer and I think slightly. I just tried this UPDATE Contacts SET _Forename = (SELECT _Forename FROM Contacts WHERE _ID = 1000002) WHERE _ID = 1000003 on my database. Note I restricted the records to be updated. I don't know what your query SELECT employeePin FROM employee WHERE "FK-ID_employee" = employee.ID_employee returns. Try it and see. It may be that it returns several employeePin if so it won't work. If it returns just 1 that would replace every emplpin in the table. Roy Lambert [Team Elevate] |
Wed, Jan 19 2011 6:53 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Oliver,
<< Well I tried it but it didn't work. Here's my actual SQL followed by the syntax error message: did I misunderstand your answer? UPDATE list_link SET (emplpin) = (SELECT employeePin FROM employee WHERE "FK-ID_employee" = employee.ID_employee) ; >> You don't need the parentheses around emplpin. Also, what are the types of the emplpin and employeePin columns ? ElevateDB is giving you an error because it thinks there's a type mismatch between the two (it wants employeePin to be an Integer type or equivalent). -- Tim Young Elevate Software www.elevatesoft.com |
This web page was last updated on Tuesday, April 30, 2024 at 03:55 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |