asked on 7/18/2008

T-SQL - update bit fields in one table from a query in another

OK - I have two tables.

HostPhotos contains UR:s to photos and the HostID - We can get a list of all the Hosts that have photos using:

SELECT HostID FROM HostPhotos GROUP BY HostID

In the HelpX_Hosts table we have added a BIT field that we want to update - setting to a value of 1 for all hosts with photos in the HostPhotos table.

We have done this in the past using a record set loop via ASP on a webpage - but it's slow and crap! - hence the move to learning SPs.

This type of procedure is something that I will use a lot throughout the site. Updating one table with values form another.

Can anyone give us an outline of the SP we need to do this for MS SQL server 2000?

Thanks

Microsoft SQL Server

Last Comment

craig32768

7/18/2008

ASKER CERTIFIED SOLUTION

chapmandew

7/18/2008

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

Guy Hengel [angelIII / a3]

7/18/2008

here we go:

UPDATE hh
  SET your_bit_field = 1
 FROM HelpX_Hosts hh
 WHERE exists ( select null from HostPhotos hp where hp.hostid = hh.hostid )

Open in new window

mherchl

7/18/2008

or

UPDATE a
SET a.bit_field = 1
FROM HelpX_Hosts a
JOIN HostPhotos b on a.HostID = b.HostID

craig32768

7/18/2008

ASKER

Brilliant - thank you so much - I usually pick up things fast but often find it difficult learning from books. Genius! Thanks