Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
tmiddled
Starting Member
1 Post |
 Posted - 2009-11-24 : 18:31:05
|
| Hi all,I am on my second day with SQL, so go easy on me :)OK, I want to update a field based on conditions. For example if field XXXX=17 & field YYYY=2 I want to set field XXXX=5. Unfortunately field XXXX is in table1 and field YYYY is in table2. They have a common value ZZZZ. So, I joined these together and created another table called temp_table. that has field XXXX, YYYY and ZZZZ.If I then update temp_table set XXXX=17 where XXXX=2 and yyyy=13 is this only going to update in the new temp_table, or will it also update table1 and table2?If it does not go through all tables, is there a way to update a joined table so it does go across all tables?Thanks in advanceTom |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
kbhere
Yak Posting Veteran
58 Posts |
Posted - 2009-11-25 : 00:09:39
|
| In SQL Server there is an option called cascade on update, which is included with the constraint.. This will help us in updating values of a column in all the tables.. If you update values in any one table it will be reflected to all the table.. Here is an example..CONSTRAINT foreign2_works1 FOREIGN KEY(project_no) REFERENCES project(project_no) ON UPDATE CASCADEThis is an example.. Kindly modify it to your requirement and try it..Balaji.K |
 |
|
|
rajdaksha
Aged Yak Warrior
595 Posts |
Posted - 2009-11-25 : 00:26:12
|
quote:
Originally posted by kbhere
In SQL Server there is an option called cascade on update, which is included with the constraint.. This will help us in updating values of a column in all the tables.. If you update values in any one table it will be reflected to all the table.. Here is an example..CONSTRAINT foreign2_works1 FOREIGN KEY(project_no) REFERENCES project(project_no) ON UPDATE CASCADEThis is an example.. Kindly modify it to your requirement and try it..Balaji.K
Refer this...The Restrict-Only level of referential integrity requires that the following four actions be detected and prevented from completing successfully: Inserting a row in a referencing table where the value of a ForeignKey does not match a PrimaryKey value in the referenced table. Updating a ForeignKey value in a row in a referencing table so there is no matching PrimaryKey value in the referenced table. Updating a PrimaryKey value in a row in a referenced table so a ForeignKey in a row in the referencing table no longer has a matching PrimaryKey value. Deleting a row in a referenced table so a ForeignKey in a row in the referencing table no longer has a matching PrimaryKey. Cascading updates and deletes provide an alternative to merely restricting the occurrence of the last two actions above. When a PrimaryKey is updated, as in number three above, a Cascading Update would cause all referencing ForeignKeys to be updated to the new PrimaryKey value. When a PrimaryKey is deleted, as in number four, a Cascading Delete would perform one of three actions: Delete the rows that referenced the deleted PrimaryKey (CASCADE).Set the referencing ForeignKey values to NULL (SET NULL).Set the referencing ForeignKey values to the column's default value (SET DEFAULT). -------------------------R... |
|
|
|
|
|
|
|
|
Updating new table