Update specific data - SQL Server Forums

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

Update specific data

Author

Topic

V.V.
Starting Member

32 Posts

Posted - 2011-09-09 : 06:04:13

Hi guys,

I need your help. Again! :)

So I have a script like this:

SELECT ...(certain fields)...
FROM dbo.A
INNER JOIN dbo.B on....
INNER JOIN dbo.C on ....
INNER JOIn dbo.D on....

UNION

SELECT ...(certain fields)...
FROM dbo.A a
INNER JOIN dbo.B on....
INNER JOIN dbo.C on ....
INNER JOIn dbo.D on....
WHERE a.id in
(
SELECT id from dbo.A
WHERE .....
)

The script above returns me exactly the rows I need to update. The question is: how to update just the result returned from the script above? If I use something like this:

UPDATE dbo.A
set idloc=11
WHERE EXISTS
(
SELECT ...(certain fields)...
FROM dbo.A
INNER JOIN dbo.B on....
INNER JOIN dbo.C on ....
INNER JOIn dbo.D on....

UNION

SELECT ...(certain fields)...
FROM dbo.A a
INNER JOIN dbo.B on....
INNER JOIN dbo.C on ....
INNER JOIn dbo.D on....
WHERE a.id in
(
SELECT id from dbo.A
WHERE .....
)
)

...is updating me all the records from dbo.A.

Please give me an idea guys. Thanks!

Kristen
Test

22859 Posts

Posted - 2011-09-09 : 06:12:02

[code]
UPDATE dbo.A
set idloc=11
WHERE SomeID IN
(
SELECT SpecificID_Column
FROM dbo.A
...
UNION
SELECT SpecificID_Column
FROM dbo.A
...
)
[/code]
or in the WHERE EXISTS you have you need to include a reference to one/many column in the outer dbo.A table so that only relevant rows are considered for action in the UPDATE section of the statement

V.V.
Starting Member

32 Posts

Posted - 2011-09-09 : 06:32:41

[code]
update A
set idloc=11
where idperson in
(
select a.idloc
from A a
inner join A a2 on a2.idperson=a.idsuperior
inner join L l on l.idlocation=a.idloc
inner join CA ca on ca.idperson=a.idperson
where a.idloc=11 and a.idsuperior=220

union all

select a.idloc
from A a
inner join A a2 on a2.idperson=a.idsuperior
inner join L l on l.idlocation=a.idloc
inner join CA ca on ca.idperson=a.idperson
where a.idloc=11 and a.idsuperior in
(
select idperson from A a
where idsuperior=220 and idcodsuc_mama=11
)
)
[/code]

Note: each IDPerson from table A has a superior in the same table identified by IDSuperior. You must do self join in table A to find the superior.

So, if I understood well, I modified the script exactly how you just told me an returns me (0 row(s) affected).

Kristen
Test

22859 Posts

Posted - 2011-09-09 : 06:39:50

"where idperson in
(
select a.idloc"

Are they the same thing and safe to join together?

V.V.
Starting Member

32 Posts

Posted - 2011-09-09 : 07:09:14

IDPerson is the primary key of table A.
IDLoc is a column in table A which shows the location must be every IDPerson. This IDLoc I have to update for the result returned by the script in the brackets.

L.E.: Sorry Kristen. Now I understand what you're saying. I modified the script putting IDPerson instead of IDLoc. Everything was just fine on update.

Thank you very much!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources