Commit Foreign Key/field Update
Jul 26, 2007is there a way to cascade update to foreign keys where both fields (PK & FK) are located in two different database?
View 2 Replies
ADVERTISEMENT
Multiple Foreign Keys On Same Field, Based On Other Field
Jul 23, 2005I have a table called BidItem which has another table calledBidAddendum related to it by foreign key. I have another table calledBidFolder which is related to both BidItem and BidAddendum, based on acolumn called RefId and one called Type, i.e. type 1 is a relationshipto BidItem and type 2 is a relationship to BidAddendum.Is there any way to specify a foreign key that will allow for thedifferent types indicating which table the relationship should existon? Or do I have to have two separate tables with identical columns(and remove the type column) ?? I would prefer not to have multipleidentical tables.
View 26 Replies View RelatedCommit Update To SQL Database Table
Feb 18, 2006The following code will not update and commit the update to a SQL Database Table. Now my where statement is looking for a Date field. Could this be the problem?
Dim DBConn As SqlConnection
Dim DBAdd As New SqlCommand
Dim strConnect As String = ConfigurationManager.ConnectionStrings("ProtoCostConnectionString").ConnectionString
DBConn = New SqlConnection(strConnect)
'Update a existing row in the table
DBAdd.CommandText = "UPDATE [D12_MIS] SET [CSJ] = @CSJ, [EST_DATE] = @EST_DATE, [RECORD_LOCK_FLAG] = @RECORD_LOCK_FLAG, [EST_CREATE_BY_NAME] = @EST_CREATE_BY_NAME, [EST_REVIEW_BY_NAME] = @EST_REVIEW_BY_NAME, [m2_1] = @m2_1, [m2_2_date] = @m2_2_date, [m2_3_date] = @m2_3_date, [m2_4_date] = @m2_4_date, [m2_5] = @m2_5, [m3_1a] = @m3_1a, [m3_1b] = @m3_1b, [m3_2a] = @m3_2a, [m3_2b] = @m3_2b, [m3_3a] = @m3_3a, [m3_3b] = @m3_3b WHERE [EST_DATE] = " & EstDateText
With DBAdd.Parameters
.AddWithValue("@CSJ", pvCSJ.Text)
.AddWithValue("@EST_DATE", tmp1Date)
.AddWithValue("@RECORD_LOCK_FLAG", tmpRecordLock)
.AddWithValue("@EST_CREATE_BY_NAME", CheckedCreator)
.AddWithValue("@EST_REVIEW_BY_NAME", CheckedReviewer)
.AddWithValue("@m2_1", vb2_1)
.AddWithValue("@m2_2_date", tmp2Date)
.AddWithValue("@m2_3_date", tmp3Date)
.AddWithValue("@m2_4_date", tmp4Date)
.AddWithValue("@m2_5", vb2_5)
.AddWithValue("@m3_1a", vb3_1a)
.AddWithValue("@m3_1b", vb3_1b)
.AddWithValue("@m3_2a", vb3_2a)
.AddWithValue("@m3_2b", vb3_2b)
.AddWithValue("@m3_3a", vb3_3a)
.AddWithValue("@m3_3b", vb3_3b)
End With
DBAdd.Connection = DBConn
DBAdd.Connection.Open()
Dim rowsAffected As Integer = 0
Try
rowsAffected = DBAdd.ExecuteNonQuery
Catch ex As Exception
tb2_2.Text = ex.ToString()
Finally
DBAdd.Connection.Close()
End Try
tb2_1.Text = rowsAffected
ADO.NET Transaction Fails To Update Database After .Commit()
Nov 24, 2003SQL Server 2000, C#, ASP.NET and ADO.NET. I have searched for 3 days trying to figure out why my ADO.NET Transaction executes my stored procedures and runs the .COMMIT() on the server, figured this out by using the SQL Profiler, but the data doesn't show up in the database tables and I recieve no error from SQL or ASP.NET. What gives???? Anyone else ever heard of such a thing. Please help, before I loose my sanity.
View 4 Replies View RelatedHow To Commit The Rest Of The Transactions Of An Update In Large Bulk?
Feb 8, 2004I have to modify the table structure where the table have a lot of data already. The log is getting full due to uncommitted transactions, there is a lot of data being updated in large bulks, not all of the transactions are committed, the update task cannot be completed.
However, there is no more spare disk space for it to commit the transaction. Anyone can help?
Transact SQL :: Multiple Update Top On Commit Transaction Doesn't Work
Jul 10, 2015I have this sql stored procedure in SQL Server 2012:
ALTER PROCEDURE [dbo].[CreateBatchAndSaveExternalCodes]
@newBatches as dbo.CreateBatchList READONLY
, @productId int
, @cLevelRatio int
, @nLevelRatio int
AS
set nocount on;
[Code] ....
Update Table1 Set Field1=null;commit; Where I Can Look For The User,who Execute This Statment?
Feb 11, 2008update table1 set field1=null where id=1; commit;
where I can look for the user,who execute this statment?
It was 6.02.2007 or 7.02.2007.
All users connect using SQL Serwer Authentication.
It is test database and from 1 to 3 users connect to it, and submit not more then 10 SQl statmant per day.
ORDER BY (field In Foreign Table)
Apr 4, 2008Newbie question. Can someone show me an SQL statement that sorts the results of a query by a field in a different table?
CREATE TABLE `Table1`
(
`table1_id` INTEGER AUTO_INCREMENT ,
`table1_name` VARCHAR(255),
PRIMARY KEY (`table1_id`)
)
CREATE TABLE `Table2`
(
`table2_id` INTEGER AUTO_INCREMENT ,
`table2_name` VARCHAR(255),
`table2_table1` INTEGER,
PRIMARY KEY (`table2_id`)
)
ALTER TABLE `Table2` ADD FOREIGN KEY (`table2_table1`) REFERENCES `Table1`(`table1_id`);
What I'd like is something like:
SELECT * FROM Table2 ORDER BY "Table1(table2_table1).tabel1_name",table2_name
It's the section in double quotes that I can't figure out how to compose.
As an example, if Table 1 has
1, A
2, B
3, C
and Table 2 has
1, a, 2
2, b, 1
3, c, 2
then I'd like the sort to return
2, b, 1
1, a, 2
3, c, 2
TIA,
Stephen
Multiple Records Tied To Foreign Key Field?
Jun 6, 2008I'm a total newb to SQL so I apologize if I butcher the description of what I'm looking to do :) Hopefully the example makes more sense. Is it possible for a single record in 1 table to contain relationships to several records in a different table?
What I've got so far is 2 tables - Hardware and Software, with ID Specifying primary keys HardwareID and SoftwareID respectively. the hardware records are for computer details and the software obviously are for programs. What I need to be able to do is pull up a hardware record and be able to see what software is installed on it, and vice versa pull up a software record to see which computers it's installed on. The problem I'm running into is that a computer can have multiple programs installed on it and a program can be installed on multiple computers, so I don't know how to create that relationship to account for that.
What I'd like to see is in the Hardware table a column for SoftwareID that has a foreign key relationship to the SoftwareID field in the Software Table, and vice versa....My question though is is that possible to do and have potentially multiple separate records it links to from that same column field? I might have a computer with say Windows XP Pro, Office 2003 Standard, Adobe Acrobat 8 and a proprietary rate calculator program that i need each to be displayed with their details when I open that computer's record. Or on the software side if I need to see which computers a license is already installed on then I want to make sure I can pull up the full list of computers.
And finally if what I'd like to do isn't possible as I described it...any recommendations for a better way?
Thanks all for the help!
Default Value Of The Foreign Key Field In A Child Table
Jul 20, 2005There are two tables, parent and child table.Foreign key field of the child table is not a required field. If thedata is submitted w/o a value into the foreign key field, what shouldI make it as a default value? Should it be "null"?
View 2 Replies View RelatedAdd Mode Problem On Field Setup With Foreign Key
May 17, 2007Hello,
I have two tables (one-to-many relation) related thru a common key. I have setup the Relation using the Database Designer. The Form is populated using Drag-and-drop which automatically adds the BindingSource, TableAdapter, and Binding Navigator. The Relation works great in the change mode, and life is good.
In the Add mode the field setup with the Foreign Key is not automatically populated as it is in Change mode. The field can be updated manually, but that defeats the purpose of setting up the Relation.
Any ideas how to fix this?
I know this is difficult to visualize without seeing it.
Thank you for any ideas,
Tom
T-SQL (SS2K8) :: Find Primary Key Table Name And Field Name Of Foreign Key
Apr 10, 2015How can i find the primary field name and primary table name of a given foreign key.
For example
Table A:
IDa
Column1
Column2
Column3
Table B:
IDb
column1
column2
column3 <---- this is foreign key to table A's IDa
What i want to do is i pass input of tableB, column3 and i get name of Primary tableA and field name IDa. How is it possible to do in tsql ?
SQL - Foreign Key With References Of Multiple Tables With Same Primary Key Field
Apr 9, 2007I want to create a table withmember id(primary key for Students,faculty and staff [Tables])and now i want to create issues[Tables] with foreign key as member idbut in references i could not able to pass on reference as orcondition for students, faculty and staff.Thank You,Chirag
View 3 Replies View RelatedDB Design :: Foreign Key Referencing Non Unique Field In Server
Oct 12, 2012I ran into an interesting situation. I'm working on contract and was looking at creating an ERD for an existing database when I ran into a problem. I found FK's that are referencing columns that do not have a unique constraint or a unique index.Â
I don't know the history of the database but was there a time in SQL Server history where this would have been possible? I scripted out the tables and created it in a test database. When I run the script to create the FK I get the following message.Â
I double checked the original tables and this FK does exist in table1 and there is no unique anything in the referenced table, table2. Currently the database is running on SQL Server 2008 Ent.
ALTER TABLE [dbo].[table1] WITH CHECK ADD CONSTRAINT [FK_table1] FOREIGN KEY([Col1])
REFERENCES [dbo].[table2] ([col2])
There are no primary or candidate keys in the referenced table 'dbo.table2' that match the referencing column list in the foreign key 'FK_table1'.
Automatic Foreign Key Update
Apr 13, 1999Hello to everyone:
Aside from using a trigger to automatically update a Foreign key,
is there a way you can automatically update a Foreign key when you
update its corresponding Primary key using the select statement?
Grigio
Update Row With Foreign Key Relationships
Mar 3, 2008Hi,
I have 2 tables Publication and Advertisment_Codes. I want to update the publication_code in both tables where country_id=1 and publication_code='A'. However, it gives me an error because of foreign key relationship. I tried first updating in Advertisment_Codes and then in Publication table but it didn't work. I tried the other way round but also it didn't work.
How can I update the publication code?
Thanks
CREATE TABLE [dbo].[Advertisment_Codes](
[Advertising_Code] [nvarchar](20) NOT NULL,
[Country_id] [int] NOT NULL CONSTRAINT [DF_Advertisment_Codes_Country_id] DEFAULT ((0)),
[Publication_code] [nvarchar](20) NULL,
[Advert_type] [nchar](1) NULL,
[Date_Entered] [datetime] NULL,
[User_Id_Ent] [int] NULL CONSTRAINT [DF_Advertisment_Codes_User_Id_Ent] DEFAULT ((0)),
[Date_Updated] [datetime] NULL,
[User_Id_Upd] [int] NULL CONSTRAINT [DF_Advertisment_Codes_User_Id_Upd] DEFAULT ((0)),
[Default_Code] [bit] NULL CONSTRAINT [DF_Advertisment_Codes_Default_Code] DEFAULT ((0)),
[Active] [bit] NULL CONSTRAINT [DF_Advertisment_Codes_Active] DEFAULT ((0)),
CONSTRAINT [PK_Advertisment_Codes_1] PRIMARY KEY CLUSTERED
(
[Advertising_Code] ASC,
[Country_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Advertisment_Codes] WITH CHECK ADD CONSTRAINT [FK_Advertisment_Codes_Countries] FOREIGN KEY([Country_id])
REFERENCES [dbo].[Countries] ([Country_id])
GO
ALTER TABLE [dbo].[Advertisment_Codes] CHECK CONSTRAINT [FK_Advertisment_Codes_Countries]
GO
ALTER TABLE [dbo].[Advertisment_Codes] WITH CHECK ADD CONSTRAINT [FK_Advertisment_Codes_Publications] FOREIGN KEY([Country_id], [Publication_code])
REFERENCES [dbo].[Publications] ([Country_id], [Publication_code])
GO
ALTER TABLE [dbo].[Advertisment_Codes] CHECK CONSTRAINT [FK_Advertisment_Codes_Publications]
CREATE TABLE [dbo].[Publications](
[Country_id] [int] NOT NULL,
[Publication_code] [nvarchar](20) NOT NULL,
[Publication_name] [nvarchar](50) NOT NULL,
[Media_type] [int] NOT NULL CONSTRAINT [DF_Puclications_Media_type] DEFAULT ((0)),
[Date_Entered] [datetime] NULL,
[User_Id_Ent] [int] NULL CONSTRAINT [DF_Publications_User_Id_Ent] DEFAULT ((0)),
[Date_Updated] [datetime] NULL,
[User_Id_Upd] [int] NULL CONSTRAINT [DF_Publications_User_Id_Upd] DEFAULT ((0)),
CONSTRAINT [PK_Puclications] PRIMARY KEY CLUSTERED
(
[Country_id] ASC,
[Publication_code] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Publications] WITH CHECK ADD CONSTRAINT [FK_Publications_Countries] FOREIGN KEY([Country_id])
REFERENCES [dbo].[Countries] ([Country_id])
GO
ALTER TABLE [dbo].[Publications] CHECK CONSTRAINT [FK_Publications_Countries]
Whisky-my beloved dog who died suddenly on the 29/06/06-I miss u so much.
Cascade Update / Foreign Key
Jul 20, 2005Hi!For the sake of simplicity, I have three tables, Employee, Department andWorkEmployee >---- Department / /^ ^WorkThe Work table have two columns, empno and depno and consists that theemployee has worked on another department.Here is my scripts:create table employee (empno int not null primary key, depno int not null)create table department (depno int not null primary key)create table work (empno int not null, depno int not null)alter table employee add constraint fk_employee_department foreign key(depno)references department (depno)on update cascadealter table work add constraint fk_work_employee foreign key (empno)references employee (empno)on update cascadealter table work add constraint fk_work_department foreign key (depno)references department (depno)on update cascadeMy problem is the last command. SQL Server responds:Server: Msg 1785, Level 16, State 1, Line 1Introducing FOREIGN KEY constraint 'fk_work_department' on table 'work' maycause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ONUPDATE NO ACTION, or modify other FOREIGN KEY constraints.But I want the depno in the work table to be updated when a department.depnochanges a value.Does anyone have a suggestion on how to overcome this problem?Thanks in advanceBest regards,Gunnar VøyenliEDB-konsulent asNORWAY
View 2 Replies View RelatedUpdate Table, Foreign Keys Etc
Aug 16, 2004I have two tables as below. I want to update a quote and change the item for which it is for. So I want to do an update statement to change the cat_ref that the quote is for. However, when I do this I get a foreign key conflict with cat_ref in the item table. How do I get around this? Thanks
Code:
CREATE TABLE Item (
cat_ref VARCHAR(5)PRIMARY KEY,
descrip VARCHAR(50),
date_added SMALLDATETIME,
cat_type VARCHAR(20))
CREATE TABLE has_quote (
quote_id INT IDENTITY (1,1) PRIMARY KEY,
date_last_pricecheck SMALLDATETIME,
cat_ref VARCHAR(5)FOREIGN KEY
REFERENCES Item(cat_ref)
ON DELETE CASCADE,
first_name VARCHAR(10),
surname VARCHAR(10),
FOREIGN KEY (first_name, surname)
REFERENCES Customer(first_name, surname)
ON DELETE CASCADE
ON UPDATE CASCADE)
UPDATE Problem With Foreign Key Constraints
Feb 9, 2004I have a table that I want to run an UPDATE statement to change some data in the table, but I get this error:
Server: Msg 547, Level 16, State 1, Line 1
UPDATE statement conflicted with COLUMN FOREIGN KEY constraint 'FK__Yield__Financial_Product__ProductCode'. The conflict occurred in database 'lsmdb', table 'Financial_Product', column 'ProductCode'.
The statement has been terminated.
How do I update the ProductCode column in both tables to reflect my updated data?
Thanks in advance.
Update Foreign Keys Using Triggers
May 18, 2008Hello,
I would like to update foreign keys using triggers. At least thats what I think is the solution when having multiple references to a table in order to avoid the "cycles or multiple cascade paths" error message.
Anyway, here are three tables
Dentist table
dentist_id int identity (auto increment)
Patient table:
patient_id int identity (auto increment)
Appointment:
apointment_id int identity (auto increment)
id_dentist int FK to dentist_id
id_patient int FK to patient_id
I am gooling but cant find a way to make a trigger run only when the dentist_id from dentist table is updated. Also, is there a way to get the new id and old id somehow? I saw some posts with new.dentist_id and old.dentist_id but apparently is not for sql server.
Thank you.
Identity Column As Foreign Key In Bulk Update
Apr 17, 2007We need to import data from flat/xml files into our database.
we plan to do so in bulk as amount of data is huge, 2GB+
we need to do some validation checks in our code after that we create insert queries.
We have identity columns that are used as foreign keys in child tables. Question is how can I write a bulk/batch insert statement that will propogate the identity column to the child, as for all other we are creating the query in the application memory.
there are 2 parent tables and 1st table value needs to be referred to in 7 tables and second table's value in 6.
Thanks much for your help.
Update Field With Trigger Only If A Specific Field Is Updated
Nov 11, 2013I want to update a field with a trigger only if a specific field is updated.
When I try the code below, it updates the field when any field in the record is updated. Is there a way to only make look at picked_dt?
ALTER TRIGGER [dbo].[UpdatePickedDate]
on [dbo].[oeordlin_sql]
after update
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
[Code] .....
SQL Server 2008 :: Update Null Enabled Field Without Interfering With Rest Of INSERT / UPDATE
Apr 16, 2015If I have a table with 1 or more Nullable fields and I want to make sure that when an INSERT or UPDATE occurs and one or more of these fields are left to NULL either explicitly or implicitly is there I can set these to non-null values without interfering with the INSERT or UPDATE in as far as the other fields in the table?
EXAMPLE:
CREATE TABLE dbo.MYTABLE(
ID NUMERIC(18,0) IDENTITY(1,1) NOT NULL,
FirstName VARCHAR(50) NULL,
LastName VARCHAR(50) NULL,
[Code] ....
If an INSERT looks like any of the following what can I do to change the NULL being assigned to DateAdded to a real date, preferable the value of GetDate() at the time of the insert? I've heard of INSTEAD of Triggers but I'm not trying tto over rise the entire INSERT or update just the on (maybe 2) fields that are being left as null or explicitly set to null. The same would apply for any UPDATE where DateModified is not specified or explicitly set to NULL. I would want to change it so that DateModified is not null on any UPDATE.
INSERT INTO dbo.MYTABLE( FirstName, LastName, DateAdded)
VALUES('John','Smith',NULL)
INSERT INTO dbo.MYTABLE( FirstName, LastName)
VALUES('John','Smith')
INSERT INTO dbo.MYTABLE( FirstName, LastName, DateAdded)
SELECT FirstName, LastName, NULL
FROM MYOTHERTABLE
Fail To Update Field With A Field Uniqueidentifier
Mar 30, 2004Hi all,
I have a problem about a query to update a table
UPDATE Email SET EmailDT='31 Mar 2004' WHERE Idx={BDF51DBD-9E4F-4990-A751-5B25D071E288}
where Idx field is a uniqueidentifier type and EmailDT is datetime type. I found that when this query calling by a VB app. then it have error "[Microsoft][ODBC SQL Server Driver]Syntax error or access violation" and i have tried again in Query Analyzer, same error also occur, the MS SQL server is version 7. Please help. thanks.
Update SQL Field With Stripped Data From Other Field
May 12, 2006Not a SQL guy but can do enough to be dangerous :)Trying to update a record. We have records that have a field with datasurrounded by some comment text such as *** Previous Public Solution*** Start and *** Previous Public Solution *** End . What I am tryingto do is write a SQL statement that will:Check that field C100 = TICKET0001 (to test with one record beforerunning on whole db)Check that field C101 is = ClosedCheck that field C102 is nullCopy field C103 data to field C102 and strip out any words such as ***Previous Public Solution *** Start and *** Previous Public Solution*** endThanks for any help!Kevin
View 1 Replies View RelatedCOMMIT TRAN Does Not Commit
Mar 28, 2008Microsoft SQL Server Management Studio Express
select @@trancount
begin tran
select @@trancount
use ProdNetPerfMon
select @@trancount
update Nodes set Caption = 'xxxx' where Vendor = 'yyyy'
select @@trancount
commit tran
select @@trancount
Executes as expected including trancount without errors.
Nodes.Caption is updated but reverts after a few minutes.
sa privileges
What am I missing?
How To Update Another Field From Combo Field
Oct 31, 2012In Access, you have a combo with column designations for example me.combofield.column(x) and you can update another field with those column(x) values.
How do you do it in MS SQL?
I didn't mean "from a 'combo' field'" in SQL(!) I just want to reproduce the equivalent of an Access combo box.
How Do I Update A Field Only If Update Value Is Not Null?
Oct 25, 2006Hi. I'm not such an expert in sql and I wanted to update a table's fields only if the value that im going to update it with is not null. How do I do that?
What I mean is something like:
---------------------------------------------------
Update database.dbo.table set
if(@newvalue !=null)
afield = @newvalue;
-------------------------------------------------
Really need your help on this. Thanks.
Update A Field
Jan 4, 2006Hello,
I have a field in a table that I have to update to the number of the week.
Information i use is from another field, which is text in the format like :
01022005
02142005
07082005
11222005
...
12022005
mmddyyyy.
How to write an update to update empty field with a number of the week based on the value from another field.
Thank you very much.
Trying To Update In A Field That Contains Xml
Nov 11, 2013Trying to run this SQL script
update Promotions
set PromotionDiscountData = '<ArrayOfPromotionRuleBase xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"><PromotionRuleBase xsi:type="StartDatePromotionRule"><StartDate>2013-11-11T00:00:00</StartDate></PromotionRuleBase><PromotionRuleBase xsi:type="ExpirationDatePromotionRule"><ExpirationDate>2014-01-12T00:00:00</ExpirationDate></PromotionRuleBase><PromotionRuleBase xsi:type="ExpirationNumberOfUsesPerCustomerPromotionRule">
[code]...
but getting this error
Msg 402, Level 16, State 1, Line 1
The data types xml and varchar are incompatible in the equal to operator.Basically within each cell I am trying to change the StartDate only
Update Field Without Retreive
Jan 24, 2007if a db field "mynum" of type int contains for example the number 543.Is there a way I can update this field without first selecting the value?so for example set the the current value to +1
Set Update Field Automatically
Feb 11, 2007in sql server I want to update the "UpdatedDate" field automatically when I change ANY other field in that specific record.it has to be set to the current server time.
View 2 Replies View RelatedUpdate A Field With Count (*)
Jan 3, 2005Hi,
I am trying to update a field in a temptable with the count of items in another table. To illustrate, CustomerID=23 and I want the number of occurences in the temp table. Here's the code which DOESN'T work:
INSERT INTO TempTable
(
CustomerID,
FirstName,
LastName,
DateAdded,
AlbumPicture,
LayoutCount
)
SELECT
Albums.CustomerID,
Customers.FirstName,
Customers.LastName,
DateAdded,
AlbumPicture,
COUNT(*) FROM Layouts WHERE Layout.customerID = Albums.CustomerID
FROM
Albums JOIN
Customers on (Albums.CustomerID=Customers.CustomerID)
Please take a look at the COUNT line. Here I want to count the occurences of a specific customerid in another table and put in into th LayoutCount field.
SQL server reports "Incorrect syntax near the keyword 'FROM'". Any ideas how to achieve this?
Many thanks!!
Eric