updating one table with info from another one?

[Rudi Ahlers]

How is this done?

Cause update comments set subscriber='Y' where comments.supplier =
company.company_name doesn't work

Yet, select comments.subscriber, comments.supplier, company.company_name
where comments.supplier = company.company_name does work....

--

Kind Regards
Rudi Ahlers
+27 (82) 926 1689

For as he thinks in his heart, so he is. ... (Proverbs 23:7)

 

[Marina]

That is because you are updating the 'comments' table, but referencing a
column in the 'company' table. So that SQL statement effectively makes no
sense.

If you are trying to update the table where the supplier exists as a row in
the company table, you could do something like:
update comments set subscriber='Y' where supplier in (select company_name
from company)

 

[Rudi Ahlers]

Thanx for the example, although it doesn't work. I tried to run it in
MSSQL Enterprise Manager, on MSSQL 2000 and I got an error saying:
Invalid object name 'company_scores'

Sorry the table name is actually company_scores, and not company like I
said in my first post.

when I run select
comments.supplier,comments.subscriber,company_scores.company_name from
company_scores,comment where company_scores.company_name =
comments.supplier, it does return those rows though

 

[Rudi Ahlers]

Thanx for the example, although it doesn't work. I tried to run it in
MSSQL Enterprise Manager, on MSSQL 2000 and I got an error saying:
Invalid object name 'company_scores'

Sorry the table name is actually company_scores, and not company like I
said in my first post.

when I run select
comments.supplier,comments.subscriber,company_scores.company_name from
company_scores,comment where company_scores.company_name =
comments.supplier, it does return those rows though

 

[Marina]

I've run queries similar to this just fine. Sounds like you have a syntax
error or something.