UPDATE using data from another table

[Guest Lin D]

I'm just learning SQL, and in my usual fashion, I'm trying something beyond what's listed in tutorials. Two tables, autograph_subjects, lins_auto_subjectslins_auto_subjects has amended data to be put into autograph_subjects. lins_auto_subjects has about a third the number of entries as autograph_subjectsData in both tables changes regularly.For every subject_id entry in lins_auto_subjects, find the matching subject_id entry in autograph_subjects, and change the data in field subject_name based on the data in the field lins_auto_subjects:changes. Rinse RepeatI tried:UPDATE autograph_subjects SET subject_name=lins_auto_subjects.changes WHERE subject_id=lins_auto_subjects.subject_idgetting error message:Unknown column 'lins_auto_subjects.subject_id' in 'where clause'I read a lot of tutorials on other commands, but can't recognize one that seems to do what I need. I can spend days trying to figure this out for myself, but I need to deliver continually/repeatedly updated data quickly. I can also drop the data into an Excel spreadsheet, and manually make the changes. Done that a couple of times, and now want to automate. And while we're at it, I need to delete entries in autograph_subjects based on data in auto_subject_deletes table, field: subject_id in both tables. For every entry in auto_subject_deletes, delete matching entry in autograph_subjects. I can create a huge string with the subject_id list in it, because any changes will only be additions to the string, which will work, but that strikes me as using a pile driver where a sledge hammer will work. Thank you.Lin

[skaterdav85]

I didnt quite understand your post completely but it sounds like you'll need to set up some kind of relationship between the 2 tables and do cascade updates and deletes through foreign keys.Or if data id changing in one table and you need it to be modifying in another table, you might be able to use a trigger, but I'm only really familiar with the concept of triggers as I haven't actually implemented one.

[[dx]]

I think you could solve it with UNION