I am trying to copy data from a linked server to a table in a new SQL
database, Here is what i have

I am trying to take the results of this query:

SELECT NUMBER
FROM PROJECTS...PROJMAST
WHERE STATUS='G'

and copy the resulting column to the column CONTRACTS.project

Here is the query that i am trying to execute:

UPDATE CONTRACTS
SET PROJECT=(
     SELECT NUMBER
     FROM PROJECTS...PROJMAST
     WHERE STATUS='G')

I get the result that no rows were updated, but if i run the select
query directly i do get all the results.  I am sure that i am missing
something simple, but i just don't see it.  Thanks.

I'm not sure what you're asking, but here's the syntax:

Use Northwind
GO


Update Products
--the first Discontinued (after "set" below) always refers to the table
above (the "products" after the update above)
Set Discontinued = remoteProducts.Discontinued
From
Products localProducts , REMOTEMACHINENAME.Northwind.dbo.Products
remoteProducts
Where
localProducts.ProductID = remoteProducts.ProductID

Make sure you can do a:

Select * from
REMOTEMACHINENAME.Northwind.dbo.Products

(replacing the machine name of course) .. if the simple select doesn't work
... then the update (above) query wont work either.



Show quoteHide quote

When you run the SELECT by itself, are you getting more than one [NUMBER] in
the resultset? (That would cause the UPDATE failure since you can only set
PROJECT to one number.

Also, it is a good practice to use complete naming and not rely upon the
defaults. For example, your code requires the database to be the current
connected db, and that the owner(schema) is the current user. Than may cause
you grief with future upgrades.

Show quoteHide quote

I do receive more than one project number, how should i adapt the query
to add any project numbers that do not already exist?

Thanks for the tip on the full text, i will try to discipline myself
better in the future :)

If I understand you correctly, you really want to ADD the resultset to the
table rather than any form of UPDATE. If that is correct, try something
like:

INSERT INTO CONTRACTS
     SELECT NUMBER
     FROM   PROJECTS...PROJMAST
     WHERE STATUS='G'

Obviously, this does not address any other columns in CONTRACTS that may
require values.
Show quoteHide quote

norrisinc wrote:
Show quoteHide quoteYou have multiple problems here...

1.  Your subquery (SELECT NUMBER FROM PROJECTS...PROJMAST WHERE
STATUS='G') can potentially return multiple records, you're doing
nothing to prevent that.

2.  Your UPDATE statement is attempting to place the results of the
subquery into a field named PROJECT.  What happens if the subquery
returns 10 records?  You can't stuff 10 records into a single field.

3.  Even if the subquery returns only a single value, your UPDATE
statement is still broken.  You haven't specified a WHERE clause to
indicate which records to update, so the single value returned by the
subquery will be placed into the PROJECT field of every record in the
CONTRACTS table.

4.  Since you haven't specified a WHERE clause, the update will touch
every record in the CONTRACTS table.  Since you get the message stating
that no rows were updated, the CONTRACTS table is apparently empty.

5.  Since CONTRACTS is apparently empty, I'm assuming it is a new table
that you're trying to populate with data from the remote PROJECTS table,
and are mistakenly using UPDATE instead of INSERT to do so.

6.  Without knowing the full structure of the CONTRACTS table, I can't
guarantee that this is the proper INSERT statement for you, but based on
what you've provided, this will populate the PROJECT field in the
CONTRACTS table with all of the NUMBER values from the remote PROJECTS
table:

INSERT INTO Contracts
SELECT Number
FROM Projects...ProjMast
    WHERE Status = 'G'

Sorry, it definitely feels like a monday, Insert is what i was looking
for, just trying to do too many things at once and confused myself.
Thanks for the help!

problems calculating a running (cumulative) total
newbie: I got problems with this stored procedure
UPDATE with WHERE CURRENT OF
Help with this select statement
Merging 4 queries into one
help on case/if in sp
create remote database from a sql file
Maintenance Plan
Generating sql script
Using field values as Column Names in another query?