Problems updating one table from another

[mmeyer27]

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:

[mmeyer27]

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

[cpb]

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.

[Ratslinger]

@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.

[mmeyer27]

Thank you for the quick, and thorough response!

[Ratslinger]

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.

[mmeyer27]

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.!