update A set Field1 = select Field1 from B

This seems pretty straigth forward but does not work. Google shows me all kinds of solutions but they all involve some sort of Join technique, but that does not work if the two tables are not related in any way.

So, how do I solve this?

Tx, Kees

Kees,

If the 2 tables are not related in any way it's hard to foresee what you are trying to do.
Can you give us an example?

--
Fernando Dias
[Team Elevate]

Kees


As Fernando says a bit more information would be helpful - like why it doesn't work. I'll guess its because "select Field1 from B" returns multiple rows eg an error along the lines of ElevateDB Error #1011 An error occurred with the query SELECT ALL "fieldname" AS "fieldname" FROM "tablename" (A scalar query can only return a single value)

You can do something like

update A set field1 = (select field1 from B range 1 to 1)

this will set all rows to the same value

The norm as Fernado indicates is to link the two tables together. In earlier versions of the SQL: standard this would have been done by using JOIN in this version you need to use sub-selects. Something along the lines of

update A set field1 = (select field1 from B where A.linkfield = B.linkfield)



Roy Lambert

Roy Lambert wrote:

Kees


As Fernando says a bit more information would be helpful - like why it doesn't work. I'll guess its because "select Field1 from B" returns multiple rows eg an error along the lines of ElevateDB Error #1011 An error occurred with the query SELECT ALL "fieldname" AS "fieldname" FROM "tablename" (A scalar query can only return a single value)

You can do something like

update A set field1 = (select field1 from B range 1 to 1)

this will set all rows to the same value

The norm as Fernado indicates is to link the two tables together. In earlier versions of the SQL: standard this would have been done by using JOIN in this version you need to use sub-selects. Something along the lines of

update A set field1 = (select field1 from B where A.linkfield = B.linkfield)



Roy Lambert

---------------------

-----------------------------------


I have got two - not related - tables. Both have a field "emailaddress". One of them is actually filled with an emailaddress. What I am trying to do is copy that emailaddress from the one table to the other.

The error presented is: "expected end of statement but instead found Field1 in UPDATE SQL statement"

tx,
Kees

Kees

Woops! I just realised that you're posting in the DBISAM group. Obviously I'm not drinking enough coffee

I switched to ElevateDB ages ago so I'm not sure if DBISAM has subselects built in. If not JOINs are what you want. The example from the manual (my old edition) is very good

UPDATE orders SET ShipToContact=Customer.Contact
FROM orders LEFT OUTER JOIN customer
ON customer.custno=orders.custno


>I have got two - not related - tables. Both have a field "emailaddress". One of them is actually filled with an emailaddress. What I am trying to do is copy that emailaddress from the one table to the other.

This is your big problem. If the two tables are not related (ie have a column in common which is either the primary key or unique) how do you expect to tell ElevateDB which row should update which row?

It may be multiple fields joined together which make the unique key. Can you post the table structure and a bit of sample data? If they're not to big, or confidential,  post the actual tables to the binaries (not this newsgroup) so we can see if we can help.

Roy