Problems updating one table from another

base

asked 2020-04-14 17:39:48 +0100

mmeyer27
5 ●2 ●2 ●3

This might be long. But here goes. I get a spreadsheet weekly that may update some fields of a database that I want to use to monitor new store openings.

I have tried many iterations of the Update statement, this is the latest attempt:

update "Stores"
    SET "Stores"."Type" = "StDevWeekly"."Type",
        "Stores"."City" = "StDevWeekly"."City",
        "Stores"."State" = "StDevWeekly"."State",
        "Stores"."Reg_cnt" = "StDevWeekly"."Reg_cnt",
        "Stores"."SO_Date" = "StDevWeekly"."SO_Date",
        "Stores"."GO_Date" = "StDevWeekly"."GO_Date",
        "Stores"."Last_Day" = "StDevWeekly"."Last_Day",
        "Stores"."Field_Tech" = "StDevWeekly"."Field_Tech"
    FROM
        "StDevWeekly"
        Where "Stores"."StNum" = "StDevWeekly"."StNum";

When I run this (or many of the other attempts) I get an error = Column not found: StDevWeekly.Type.

However, when I run simple select queries, it finds it just fine. I even had one that showed both tables fields (with a modified reg_cnt in the stdevweekly table).

Any help would be greatly appreciated. It's been driving me nuts. And most normal SQL tutorials, etc, seem to not work very well in base. :(

My tables are this:

image description

edit retag flag offensive close merge delete

Comments

Oh, I am easily able to import the spreadsheet into the StDevWeekly table. (sorry, I forgot that part)

mmeyer27 ( 2020-04-14 17:48:59 +0100 )edit

add a comment

2 Answers

Sort by » oldest newest most voted

answered 2020-04-15 02:34:40 +0100

cpb
156 ●3 ●5

this is my second post on this site. my first post failed to draw a response/clarification from the topics author and i decided that my first contribution would also be my last.

ability will vary according to experience and determination but common decency is a character trait which we either do or do not possess.

we all need a helping hand occasionally, i am obviously unable to test but please try this code.

update "Stores"
    SET
        "Type" = (select "Type" from "StDevWeekly" where "StNum" = "Stores"."StNum"),
        "City" = (select "City" from "StDevWeekly" where "StNum" = "Stores"."StNum"),
        "State" = (select "State" from "StDevWeekly" where "StNum" = "Stores"."StNum"),
        "Reg_cnt" = (select "Reg_cnt" from "StDevWeekly" where "StNum" = "Stores"."StNum"),
        "SO_Date" = (select "SO_Date" from "StDevWeekly" where "StNum" = "Stores"."StNum"),
        "GO_Date" = (select "GO_Date" from "StDevWeekly" where "StNum" = "Stores"."StNum"),
        "Last_Day" = (select "Last_Day" from "StDevWeekly" where "StNum" = "Stores"."StNum"),
        "Field_Tech" = (select "Field_Tech" from "StDevWeekly" where "StNum" = "Stores"."StNum")
where 1 in
            (select 1 from "StDevWeekly" where "StNum" = "Stores"."StNum")

the select clauses within the main body e.g.

(select "Type" from "StDevWeekly" where "StNum" = "Stores"."StNum")

will always return a result.

when the WHERE clause proves TRUE then "Type" is returned else NULL is returned and your table is updated accordingly.

the clause

where 1 in
        (select 1 from "StDevWeekly" where "StNum" = "Stores"."StNum")

ensures that we only update the current table row when the returned value is NOT NULL.

edit flag offensive delete link

Comments

@cpb,

...and i decided that my first contribution would also be my last.

Bad decision. This was a good answer and a a quick test using my tables shows it works.

Keep posting.

Edit:

As a note, early in my posting I kept track of respondents. Over 50% of my posts (this # was a sample of over 500) received no response of any kind - no accepted answer, up or down vote or comment. Point being be prepared to be ignored. Happens more often than you can imagine.

Ratslinger ( 2020-04-15 02:55:48 +0100 )edit

Thank you for the quick, and thorough response!

mmeyer27 ( 2020-04-15 20:29:51 +0100 )edit

add a comment

answered 2020-04-14 20:51:01 +0100

Ratslinger
26631 ●9 ●35 ●135

updated 2020-04-15 05:01:04 +0100

Hello,

You did not state what database you were using as this could play a role in the syntax used.

This is based upon HSQLDB embedded - Firebird embedded s/b same.

There is no From in an UPDATE statement. ( See -> HSQLDB - UPDATE). When updating from another table you need to SELECT the value wanted:

Edit (table name mistake):

SET "Type" = (Select "Type" from "StDevWeekly" Where "StDevWeekly"."StNum" = "Stores"."StNum")

Edit:

Correcting original post after some additional testing.

Am not certain as to how to do this with multiple fields along with multiple records.

Also your comment:

And most normal SQL tutorials, etc, seem to not work very well in base. :(

is most likely due to using HSQLDB embedded in Base which is very old and the tutorials are using newer databases.

Edit #2 (2020-04-14):

Here is a post with updating one field for many different records -> Updating a table field with data from another table. See first edit in the answer.

Still no answer to multiple fields with multiple records for HSQLDB embedded.

edit flag offensive delete link

Comments

Thank you! And yes, you are correct, I am using HSQLDB. If I was allowed to accept both answers, I would, but it won't seem to let me do that.!

mmeyer27 ( 2020-04-15 20:30:39 +0100 )edit

add a comment

Problems updating one table from another

Comments

2 Answers

Comments

Comments

Question Tools

Stats

Related questions

Problems updating one table from another edit

Comments

2 Answers

Comments

Comments

Question Tools

Stats

Related questions

Problems updating one table from another