Update WHERE checking with another database

[JJBreaker]

Hi, I'm Spanish and little English, I will try to understand me best.

I am creating an online game based on xml, lua and SQL Server, I have a database named accountdb and other named gamedb.

within accountdb This table accounts and within gamedb This table characters... Ok

in accountdb.accounts field site_code INT
in gamedb.characters field online INT

I need an update site_code set all rows to 1 if online is 0

For example:

UPDATE accountdb.dbo.accounts SET site_code = 1 WHERE online = 0
 

The script runs on gamedb...

If I could help I would appreciate forever.

Greetings.

[mjhennig]

You could write a trigger that applies ON UPDATE:
http://msdn.microsoft.com/en-us/library/ms189799.aspx

Otherwise you may use your application code to execute both queries (SET online = 0 and the other one you've mentioned), but that's considered bad practice.

[JJBreaker]

thank you very much for the quick response, but is somewhat more complicated...

I need to check if any of yours characters is online, if not any of his characters gamedb online, update accoundb.site_code to 1 :/

[JJBreaker]

Thanks for the help, I resolved, I leave here the code if it helps someone

Declare @ID numeric
      Select @ID = (SELECT COUNT ("ONLINE") AS ONLINE FROM RZ_CHARACTER WHERE ACCN_ID = @ACCN_ID AND ONLINE = 1)
 
      IF @ID = 0      
    UPDATE RZ_ACCOUNTDB.dbo.RZ_ACCOUNT SET SITE_CODE=1 WHERE SITE_CODE='' AND ACCN_ID=@ACCN_ID;  
Else
    UPDATE RZ_ACCOUNTDB.dbo.RZ_ACCOUNT SET SITE_CODE='' WHERE SITE_CODE='1' AND ACCN_ID=@ACCN_ID;  

[NeoPa]

How would that work (not fail)?

If [Site_Code] is numeric then setting it to '' will fail. If [Site_Code] is textual then setting it to 1 will fail. As you do both in the same SQL I would expect it to fail.

[JJBreaker]

xD yes... any ideas? I'm just using google translator and how to describe exactly what I'm ...

http://www.foroz.org/foroz/viewtopic...=582945#582945

[NeoPa]

I have really no idea how to respond to that. You say that works, then you clearly indicate you know that it doesn't. What is there that I can sensibly respond to :-S