[Solved] Updating table with fields from another table

[cwdavi1]

Windows 10, OO Version: 6.3.2.0.0+ (x64) with Firebird.

I haven't touched SQL in over 25 years and I'm having a lot of trouble with this trivial application. I want to find rows in Table1 with matching name in Table2, then update matched rows in Table1 with the AID field from Table2. I keep getting confused with differences between other versions of SQL and Firebird. I've learned to search Firebird 2.5 documentation but I still can't find what I need. I've learned how to create a stored procedure but apparently can't call it from inside Base without a macro, which I want to avoid. I created a view of the query to simplify the rest but I'm still floundering.

I've also had trouble using Base Tools->SQL. I don't understand exactly what I can and can't do with it. I'm sure that this can be done very simply but I'm lost. I'm not as sharp as I was when I was younger (not that I was all that sharp back then).

I will greatly appreciate any help. Thank you, Wayne

Table1:
ID NAME AID
0 who am I null
1 what me worry null
2 lost in space null
3 grumpy old man null

Table2:
ID NAME AID
123 up to you 6789
124 it wasn't me 3214
666 the clock is stopped 1726
777 who am I 5432
999 grumpy old man 4321

CREATE OR ALTER VIEW UPDATEVIEW (ID, AID)
AS SELECT DISTINCT "Table1"."ID", "Table2"."AID"
FROM "Table1"
JOIN "Table2"
ON "Table1"."NAME" = "Table2"."NAME";

SELECT * FROM UPDATEVIEW

gives:
Table1 ID Table2 ID
0, 5432,
3, 4321,


Now I want to update Table1 to:

ID NAME AID
0 who am I 5432
1 what me worry null
2 lost in space null
3 grumpy old man 4321

I've tried all sorts of things like these but can't find a solution that works:
UPDATE "Table1"
SET "Table1"."AID" = "UPDATEVIEW"."aid"
FROM UPDATEVIEW
WHERE "Table1"."ID" = "UPDATEVIEW"."ID";

or with a cursor:

FOR temprow in
SELECT * from UPDATEVIEW
LOOP
UPDATE "Table1" SET "Table1"."AID" = temprow."AID" where "Table1"."ID" = temprow."ID"
END LOOP;

[FJCC]

There is an example here where the user wants to update several columns in the table CLIENTS with values from Tabla_Clients. The forum is in Spanish but I don't think you need to look at anything other than the SQL code. The code is for HSQLDB.

[cwdavi1]

Thank you very much. I distilled what I needed down to:

UPDATE "Table1"
SET "Table1"."AID" = (SELECT "AID" FROM "Table2" WHERE "Table1"."NAME" = "Table2"."NAME")
WHERE "Table1"."NAME" IN (SELECT "NAME" FROM "Table2")

I was positive that it wouldn't work but it did. When my mind is clear I'll try to figure it out.

[cwdavi1]

It worked for the simple tables I posted. When I ran it against real data I got a cardinality violation. I have some weird data. I added DISTINCT to the query and it worked.

UPDATE "Table1"
SET "Table1"."AID" = (SELECT DISTINCT "AID" FROM "Table2" WHERE "Table1"."NAME" = "Table2"."NAME")
WHERE "Table1"."NAME" IN (SELECT "NAME" FROM "Table2")