Regarding Update Query......
-
Hi!!!!
Can tell me why this is not working , and if this possible then how that will be???????
CREATE TABLE REGSIST (ID INT)
CREATE TABLE GRANTED (ID INT)
INSERT INTO REGSIST VALUES (78)
INSERT INTO REGSIST VALUES (70)
INSERT INTO REGSIST VALUES (767)
INSERT INTO REGSIST VALUES (778)
INSERT INTO REGSIST VALUES (788)
INSERT INTO REGSIST VALUES (798)
INSERT INTO GRANTED VALUES (658)
INSERT INTO GRANTED VALUES (628)
INSERT INTO GRANTED VALUES (678)
INSERT INTO GRANTED VALUES (648)
INSERT INTO GRANTED VALUES (688)
INSERT INTO GRANTED VALUES (698)
UPDATE REGSIST
SET ID = (SELECT ID FROM GRANTED)
I am getting error..
Server: Msg 512, Level 16, State 1, Line 18
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.
Regards,
Papillon
-
This ain't ever gonna work... what is the expected output you want from the update?
-
Hey RGS'us!!!!!!!
I want to update IDs in REGSIST table by IDs in GRANTED......
Is that possible.......
Regards,
Papillon
-
SHOW ME the results, there's no logical link I can see with the info you gave me.
-
The embedded select statement in your update DML returns 6 values (658,628,678,648,688,698). The update will only work if the select statement returns ONE value.
-
Not possible the way you are trying to do the update.
Correct me if I am wrong. You would like to do a positional update, in other words, you would like the ID column in the first row of the REGSIST table be
updated with the ID value from the first row from the Granted table.
What you could do is something like:
UPDATE reg
SET reg.ID = grn.ID
FROM REGSIST reg inner join GRANTED grn
on reg.ID = grn.ID
In some fashion or other, you have to match the rows from the REGSIST table with the rows from the Granted table. With the curent data, no rows would be updated.
A second idea would be to add an identity column to each table, then join on the identity column. That positional update could then be represented within an inner join. Drop the identity columns after the update.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply