[Solved] Updating table with fields from another table

Creating tables and queries

[Solved] Updating table with fields from another table

Postby cwdavi1 » Sat Sep 21, 2019 10:42 pm

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;
Last edited by cwdavi1 on Sun Sep 22, 2019 5:10 am, edited 1 time in total.
Libreoffice 6.3 on Windows 10 Pro
cwdavi1
 
Posts: 29
Joined: Fri Sep 13, 2019 9:47 pm

Re: updating table with fields from another table

Postby FJCC » Sun Sep 22, 2019 12:39 am

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.
Windows 10 and Linux Mint, since 2017
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 7751
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: [SOLVED] updating table with fields from another table

Postby cwdavi1 » Sun Sep 22, 2019 5:13 am

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.
Libreoffice 6.3 on Windows 10 Pro
cwdavi1
 
Posts: 29
Joined: Fri Sep 13, 2019 9:47 pm

Re: [SOLVED] updating table with fields from another table

Postby cwdavi1 » Sun Sep 22, 2019 5:50 am

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")
Libreoffice 6.3 on Windows 10 Pro
cwdavi1
 
Posts: 29
Joined: Fri Sep 13, 2019 9:47 pm


Return to Tables & Queries

Who is online

Users browsing this forum: No registered users and 0 guests