• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 39
  • Last Modified:

sql query refrence data on a alternate table to change specific values to false

i have 2 tables, the primary table [dbo.subscriber data] has several columns. the columns i need to reference on the primary table are 'AccountID' and 'AcctLineCode'
the other table is [dbo.subscriber Status], it also has the 'AccountID' column as well as the column 'IsRecurring'
i need to change all instances of 'IsRecurring' on [dbo.subscriber Status] to False if it has an 'AccountID' that matches the 'AccountID' from [dbo.subscriber data] that has an 'AcctLineCode' of 'HR' or 'HRS'

I assume i need to use inner join, i have no idea the syntax
0
csePixelated
Asked:
csePixelated
  • 9
  • 3
  • 2
2 Solutions
 
awking00Commented:
update [dbo.subscriber Status] s
set IsRecurring = False
where exists
(select 1 from [dbo_subscriber Data] d
 where d.AccountID = s.AccountID
 and d.AcctLineCode like 'HR_');
1
 
csePixelatedAuthor Commented:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 's'.
0
 
csePixelatedAuthor Commented:
removed the s from line one and the d from line 4, checked sql passed, ran it and got...
Msg 208, Level 16, State 1, Line 1
Invalid object name 'dbo.subscriber Status'.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
csePixelatedAuthor Commented:
update [dbo.subscriber Status]
set IsRecurring = False
where exists
(select 1 from [dbo_subscriber Data] d
 where d.AccountID = s.AccountID
 and d.AcctLineCode like 'HR_');

ran the above
still got
Msg 208, Level 16, State 1, Line 1
Invalid object name 'dbo.subscriber Status'.
0
 
awking00Commented:
What are the actual case sensitive names of your tables
0
 
csePixelatedAuthor Commented:
[dbo.Subscriber Data] & [dbo.Subscriber Status] are the actual case sensitive names of the tables.
i have now tryed with these corected...

update [dbo.Subscriber Status]s
set IsRecurring = False
where exists
(select 1 from [dbo_Subscriber Data] d
 where d.AccountID = s.AccountID
 and d.AcctLineCode like 'HR_');

still getting...
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 's'.
0
 
csePixelatedAuthor Commented:
noticed a lack of space...
update [dbo.Subscriber Status] s
set IsRecurring = False
where exists
(select 1 from [dbo_Subscriber Data] d
 where d.AccountID = s.AccountID
 and d.AcctLineCode like 'HR_');
still
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 's'.
0
 
awking00Commented:
Might need AS keyword?
update [dbo.Subscriber Status] AS  s
 set IsRecurring = False
 where exists
 (select 1 from [dbo_Subscriber Data] AS d
  where d.AccountID = s.AccountID
  and d.AcctLineCode like 'HR_');
0
 
csePixelatedAuthor Commented:
update [dbo.Subscriber Status] AS "s"
 set IsRecurring = False
 where exists
 (select 1 from [dbo_Subscriber Data] AS "d"
  where d.AccountID = s.AccountID
  and d.AcctLineCode like 'HR_');

Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'AS'.

update [dbo.Subscriber Status] AS s
 set IsRecurring = False
 where exists
 (select 1 from [dbo_Subscriber Data] AS d
  where d.AccountID = s.AccountID
  and d.AcctLineCode like 'HR_');

Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'AS'.
0
 
csePixelatedAuthor Commented:
i was curious as to weather i needed the In clause, did some playing around

update [Subscriber Status]
 set IsRecurring = 'False'
 where exists
 (select 1 from [Subscriber Data] AS d
  where d.AccountID = AccountID
  and d.AcctLineCode like 'HR_');

this ran however too many rows were affected
0
 
csePixelatedAuthor Commented:
yea it changed every IsRecurring to 'false'
0
 
Scott PletcherSenior DBACommented:
UPDATE ss
SET IsRecurring = 0 /*'F'*/
FROM [dbo.subscriber Status] ss
INNER JOIN (
    SELECT AccountID
    FROM [dbo.subscriber data]
    WHERE AcctLineCode IN ('HR', 'HRS')
    GROUP BY AccountID
) AS sd ON sd.AccountID = ss.AccountID
1
 
csePixelatedAuthor Commented:
aparently it did not like the "dbo."

UPDATE ss
SET IsRecurring = 0 /*'F'*/
FROM [Subscriber Status] ss
INNER JOIN (
    SELECT AccountID
    FROM [Subscriber Data]
    WHERE AcctLineCode IN ('HR', 'HRS')
    GROUP BY AccountID
) AS sd ON sd.AccountID = ss.AccountID

the above worked perfectly..,. TY Scott P and Awking00..!!
0
 
Scott PletcherSenior DBACommented:
D'OH, sorry, the "dbo." should have been separate, before the brackets.

UPDATE ss
SET IsRecurring = 0
FROM dbo.[subscriber Status] ss
INNER JOIN (
    SELECT AccountID
    FROM dbo.[subscriber data]
    WHERE AcctLineCode IN ('HR', 'HRS')
    GROUP BY AccountID
) AS sd ON sd.AccountID = ss.AccountID

Btw, ALWAYS use an alias when UPDATEing a table using a JOIN.  Otherwise, it's too easy to UPDATE all rows and/or the wrong rows in the table being updated.  Like above:

UPDATE ss --<< alias name (not [subscriber Status] directly)
SET IsRecurring = 0
FROM dbo.[subscriber Status] ss --<< assign alias name for use in UPDATE
1
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 9
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now