Update table from View

Question

I have the following view called cont_struct_breaks:

pipe_segment_reference  com_struct_score    com_defects com_struct_index
2610                         353               111            3.2
3988                         266               97             2.7
5632                         49                22             2.2

I have another table called structural_rating with fields

pipe_segment_reference, structural_score structural_defects, structural_index

I want to UPDATE values in "structural_rating" from "cont_struct_breaks" WHERE the pipe_segment_reference matches.

I cannot figure this out however, I tired doing something like this but no luck.

Attempted This

[EDIT]

The cont_struct_breaks view is create with a select statement which looks like this:

SELECT
    structural_rating.Pipe_Segment_Reference,
    cont_struct + structural_score AS com_struct_score,
    cont_struct_d + structural_defects AS com_defects,
    ROUND(com_struct_score / com_defects, 1) AS com_struct_index
FROM ...

UPDATE structural_ratings table from the select statement above if easier than the view.

Is this a linked database, or is it all native Access? If it's linked, is this native (pass-thru) SQL you are doing or an Access update on the linked table. Also, if linked, what is the RDBMS, MS SQL Server? Unfortunately, even though a good 80% of SQL is common across RDBMS platforms, update queries are one of the areas there they differ widely. — Hambone, Jan 26 '16 at 03:37

score 0 · Answer 1 · answered Jan 25 '16 at 19:39

0

Doing a straight-up replace of your info for what's in the answer at that linked answer, I got this:

UPDATE
  structural_rating
SET
  structural_rating.structural_score = RAN.com_struct_score
FROM
  structural_rating SI
INNER JOIN
  cont_struct_breaks RAN
ON 
  SI.pipe_segment_reference = RAN.pipe_segment_reference

You'll probably want to change the names of the aliases to reduce confusion, but it'll work just fine the way it is.

So now you want to update additional fields. When you update multiple fields at once, you just separate them with commas:

SET
  structural_rating.structural_score = RAN.com_struct_score,
  structural_rating.structural_defects = RAN.com_defects,
  structural_rating.structural_index = RAN.com_struct_index

So, your complete query should be:

UPDATE
  structural_rating
SET
  structural_rating.structural_score = RAN.com_struct_score,
  structural_rating.structural_defects = RAN.com_defects,
  structural_rating.structural_index = RAN.com_struct_index
FROM
  structural_rating SI
INNER JOIN
  cont_struct_breaks RAN
ON 
  SI.pipe_segment_reference = RAN.pipe_segment_reference

answered Jan 25 '16 at 19:39

Johnny Bones

8,786
7
52
117

I get error "Syntax Error (missing operator in query expression starting at RAN.com_struct_index)" – Tristan Forward Jan 25 '16 at 19:48
Must by a syntax or field name issue. See this answer to a similar question, my syntax structure is identical: http://stackoverflow.com/a/9079688/2174085 – Johnny Bones Jan 25 '16 at 20:10
I don't think will work that link is for sql-server. I using MS Access. – Tristan Forward Jan 25 '16 at 20:11
Also, check your data. Add Trim() or Nz() where appropriate ( * i.e. structural_rating.structural_index = Nz(Trim(RAN.com_struct_index)) * ). – Johnny Bones Jan 25 '16 at 20:16
SQL is SQL. The syntax for an Update statement is the same in both Access and SQL Server. – Johnny Bones Jan 25 '16 at 20:16
Data is fine it has be checked. You posted same answer as in the link I provided which I already tried it does not work. – Tristan Forward Jan 25 '16 at 20:22
Are you sure none of your field names has spaces in it? This happens sometimes when a field name has spaces in it. Also, make sure the data type is the same for both structural_index and com_struct_index. That error will occur if it's trying to set a field with one data type equal to a field with a different data type. – Johnny Bones Jan 25 '16 at 20:29
It has something to do with what is described here in comments by user beach: http://stackoverflow.com/questions/871905/use-select-inside-an-update-query – Tristan Forward Jan 25 '16 at 20:41
Just to be sure, the error indicates an issue with the "index" piece of it. Have you tried removing the "index" piece of the SET section and just keeping the "score" and "defects" lines? Just to see if it will update those 2? – Johnny Bones Jan 25 '16 at 20:44
I think the database is corrupted, I'm working on a copy and things seem to be going better. Will update tomorrow – Tristan Forward Jan 25 '16 at 20:58

score 0 · Accepted Answer · answered Jan 26 '16 at 14:57

I ended up doing this writing to new table instead of view then running this:

UPDATE structural_rating
INNER JOIN cont_struct
ON structural_rating.pipe_segment_reference = cont_struct.pipe_segment_reference
SET
structural_score = cont_struct.com_struct_score,    
structural_defects = cont_struct.com_defects,     
structural_index = cont_struct.com_struct_index;

Update table from View

2 Answers2