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

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]

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]

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]

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]

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