May 1, 2012 at 6:35 pm
hi Guy , please can somebody explain me how i can do to update my table ? hier ist one example:
/***************************
Create different table and foreign key
****************************/
CREATE TABLE Family1(
ID_F1 int IDENTITY(1,1) NOT NULL,
Name varchar(50) NOT NULL,
Old int NOT NULL,
Children int )
----------------------------------------
CREATE TABLE Family2(
ID_F2 int IDENTITY(1,1) NOT NULL,
Name varchar(50) NOT NULL,
Old int NOT NULL ,
Children int NULL)
-----------------------------------------
CREATE TABLE Dependence(
[Data_AbID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
[ID_F1] [int] NOT NULL,
[ID_F2] [int] NULL,
[Dependance] [varchar](50) NULL)
------------------------------------------
SET ANSI_PADDING OFF
GO
ALTER TABLE dependence WITH CHECK ADD CONSTRAINT [FK_F1] FOREIGN KEY(ID)
REFERENCES family1 (ID)
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE dependence WITH CHECK ADD CONSTRAINT [FK_F2] FOREIGN KEY(ID)
REFERENCES Family2 (ID)
ON UPDATE CASCADE
ON DELETE CASCADE
GO
/***************************
Fill my tables
****************************/
insert into dependence
(ID_F1,ID_F2)
values
(1,3),(1,5),(2,null),(3,4),(4,1),(5,2)
--------------------
insert into Family2
(Name,Old)
values
('F',28), ('G',90), ('H',72), ('J',12), ('K',3)
-------------------------------------------
insert into Family1
(Name,Old)
values
('A',1), ('B',3), ('C',7), ('D',12), ('E',34)
----------------------------------------------
/*********************************
Create my View V_Dependance
*********************************/
create view V_dependance
(Name,DependOf) as
select A.Name,AD.Name from dependence At
Left outer join
Family1 A on
At.ID_F1 = A.ID
left outer join
Family2 AD on
At.ID_F2 = AD.ID
------------------------------------------
/*********************************
Now I want to update my Children Column in Family1 with help of
my View and it is here where i get a problem
***********************************/
UPDATE Family1 SET Children = 3
WHERE Name = (SELECT Name FROM v_dependance WHERE dependOf IS NULL)
--feeback: it don't walk because many rows are affected
-- have somebody an idea how i can do??
-------------------------------------
May 1, 2012 at 7:20 pm
Change the = to IN?
UPDATE Family1 SET Children = 3
WHERE Name IN (SELECT Name FROM v_dependance WHERE dependOf IS NULL)
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 2, 2012 at 3:16 am
thanks it walk but i gat another problem please it is in german
/**
* when i try to change this operator '= ' to 'IN' i get a failure
**/
------ with = failure
/*
*Msg 512, Level 16, State 1, Line 1
*Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, *<= , >, >= or when the subquery is used as an expression.
*The statement has been terminated.
*/
UPDATE Netzplan SET Frühester_Start = (SELECT Frühestere_Ende FROM Netzplan WHERE V_Name IN
(SELECT VorgangName FROM vSicht_Abhängigkeit WHERE dependent IS NULL))
WHERE V_Name IN (SELECT VorgangName FROM vSicht_Abhängigkeit WHERE dependent IN
(SELECT VorgangName FROM vSicht_Abhängigkeit WHERE Abhängigkeiten IS NULL))
--------WITH IN
/*
* get this failure
*Msg 156, Level 15, State 1, Line 1
*Incorrect syntax near the keyword 'in'.
*Msg 156, Level 15, State 1, Line 3
*Incorrect syntax near the keyword 'WHERE'.
*/
UPDATE Netzplan SET Frühester_Start IN (SELECT Frühestere_Ende FROM Netzplan WHERE V_Name IN
(SELECT VorgangName FROM vSicht_Abhängigkeit WHERE dependent IS NULL))
WHERE V_Name IN (SELECT VorgangName FROM vSicht_Abhängigkeit WHERE dependent IN
(SELECT VorgangName FROM vSicht_Abhängigkeit WHERE Abhängigkeiten IS NULL))
---please how can i do here??
May 2, 2012 at 3:27 am
inf1154 (5/2/2012)
thanks it walk but i gat another problem please it is in german
/**
* when i try to change this operator '= ' to 'IN' i get a failure
**/
------ with = failure
/*
*Msg 512, Level 16, State 1, Line 1
*Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, *<= , >, >= or when the subquery is used as an expression.
*The statement has been terminated.
*/
UPDATE Netzplan SET Frühester_Start = (SELECT Frühestere_Ende FROM Netzplan WHERE V_Name IN
(SELECT VorgangName FROM vSicht_Abhängigkeit WHERE dependent IS NULL))
WHERE V_Name IN (SELECT VorgangName FROM vSicht_Abhängigkeit WHERE dependent IN
(SELECT VorgangName FROM vSicht_Abhängigkeit WHERE Abhängigkeiten IS NULL))
--------WITH IN
/*
* get this failure
*Msg 156, Level 15, State 1, Line 1
*Incorrect syntax near the keyword 'in'.
*Msg 156, Level 15, State 1, Line 3
*Incorrect syntax near the keyword 'WHERE'.
*/
UPDATE Netzplan SET Frühester_Start IN (SELECT Frühestere_Ende FROM Netzplan WHERE V_Name IN
(SELECT VorgangName FROM vSicht_Abhängigkeit WHERE dependent IS NULL))
WHERE V_Name IN (SELECT VorgangName FROM vSicht_Abhängigkeit WHERE dependent IN
(SELECT VorgangName FROM vSicht_Abhängigkeit WHERE Abhängigkeiten IS NULL))
---please how can i do here??
These tables are very different from the DDL's that you have posted but I can say that you can't do this:
UPDATE Netzplan SET Frühester_Start IN
This is why you are receiving the error on your second query
The first query is erroring due to one of your subqueries returning more than one value as the error states
Sorry I can't be more help but there is not a lot to go on here
==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe
May 2, 2012 at 6:53 am
I think that this is just a language problem. When Sean said to replace = with IN, he only meant the one in the WHERE clause, not all of them. The SET clause requires an =.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
May 2, 2012 at 7:04 am
Hi guy i have a problem when i update a data may one of can help me.
since this morning i try to found a solution by myself without success. I was already ask something here it helps me but for a few moment i think all thing were ok but i'm still a biginner.
Please forgive me because they are some variable in German because i study here.
here is the code
/****
*create a different table
****/
CREATE TABLE AbhängigkeitTabelle(
AB_ID int IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED ,
Vorgang_ID_Netzplan2 int NOT NULL,
ABhängigkeiten_ID_Netzplan int NULL,
)
CREATE TABLE Netzplan_2(
Vorgang_ID int IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED ,
Abhängigkeiten varchar(50) NOT NULL,
Dauer int NOT NULL,
F_Anfang int NULL,
F_Ende int NULL,
S_Anfang int NULL,
S_Ende int NULL,
Puffer int NULL,
)
CREATE TABLE Netzplan(
V_ID int IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
V_Name varchar(50) NOT NULL,
Dauer int NOT NULL,
Frühester_Anfang int NULL,
Frühestere_Ende int NULL,
Späterer_Anfang int NULL,
Späterere_Ende int NULL,
Puffer int NULL,
)
/*---------------------------------------------
*Create a view
*----------------------------------------------*/
CREATE VIEWvSicht_Abhängigkeit AS
SELECTA.V_NameAS VorgangName,
A.DauerAS Dauer,
AD.AbhängigkeitenAS Abhängigkeiten
FROMAbhängigkeitTabelleAS ATLEFT OUTER JOINNetzplanAS AON AT.Vorgang_ID = A.V_ID
LEFT OUTER JOIN Netzplan_2AS ADON AT.ABhängigkeiten_ID = AD.Vorgang_ID
/*************
*Now i insert a Value in table natzplan and natzplan_2
*************/
insert into Netzplan
(V_Name,Dauer)
values
('A',25),('B',20),('C',40),('D',50),('E',30),('F',30)
-------------------------------
insert into Netzplan_2
(Abhängigkeiten,Dauer)
values
('A',25),('B',20),('C',40),('D',50),('E',30),('F',30)
/******
*Now i made a connection between Netzplan and Natzplan_2 with hel of AbhängigkeitTabelle
AbhängigkeitTabelle is my classification table oder assignement table
******/
INSERT INTO AbhängigkeitTabelle
(Vorgang_ID ,ABhängigkeiten_ID)
VALUES
(1,2),(1,3),(2,6), (3,Null), (4,6),(5,1), (6,Null)
/**********
*I try to do a Update
this first and second UPDATE walk but the third don't walk
***********/
------It run ok-----
UPDATE Netzplan SET Frühester_Anfang = 0
WHERE V_Name IN (SELECT VorgangName FROM vSicht_Abhängigkeit WHERE Abhängigkeiten IS NULL)
-----It Run ok----
UPDATE Netzplan SET Frühestere_Ende = (Frühester_Anfang + Dauer)
WHERE V_Name IN (SELECT VorgangName FROM vSicht_Abhängigkeit WHERE Abhängigkeiten IS NULL)
------THIS DON'T RUN ----
UPDATE Netzplan SET Frühester_Anfang IN (SELECT Frühestere_Ende FROM Netzplan WHERE
V_ID IN (SELECT Vorgang_ID FROM AbhängigkeitTabelle WHERE ABhängigkeiten_ID IS NULL))
WHERE V_ID = (SELECT ABhängigkeiten_ID FROM AbhängigkeitTabelle WHERE ABhängigkeiten_ID IN
(SELECT Vorgang_ID FROM AbhängigkeitTabelle WHERE ABhängigkeiten_ID IS NULL))
------OR Like this it not run too---
UPDATE Netzplan SET Frühester_Anfang = (SELECT Frühestere_Ende FROM Netzplan WHERE
V_ID IN (SELECT Vorgang_ID FROM AbhängigkeitTabelle WHERE ABhängigkeiten_ID IS NULL))
WHERE V_ID = (SELECT ABhängigkeiten_ID FROM AbhängigkeitTabelle WHERE ABhängigkeiten_ID IN
(SELECT Vorgang_ID FROM AbhängigkeitTabelle WHERE ABhängigkeiten_ID IS NULL))
-------I try to simplify like this but it not run-------
---PLEASE has somebody an idea how i can do???---
UPDATE Netzplan SET Frühester_Anfang = (SELECT Frühestere_Ende FROM Netzplan WHERE
V_Name IN (SELECT Abhängigkeiten FROM vSicht_Abhängigkeit WHERE Abhängigkeiten IS NOT NULL))
-----------
/*****
*Failure: Msg 512, Level 16, State 1, Line 1
*Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <,
* <= , >, >= or when the subquery is used as an expression.
*The statement has been terminated.
************/
-----What can i do please??
May 3, 2012 at 8:23 am
Looks like you missed one change of IN to ''="
UPDATE Netzplan SET Frühester_Anfang IN vs. UPDATE Netzplan SET Frühester_Anfang =
May 12, 2012 at 8:34 am
Hi,
I think below code might work for you,I am not sure but have a try on it.
UPDATE Family1 SET Children = 3 from Family1 f1 inner join v_dependance vd on (f1.name = vd.name ) WHERE vd.dependOf IS NULL
I am also newbei if any mistakes then please correct me.:-)
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply