UPDATE to one table based on select from another

Topic

New Reply

I am trying to update rows in one table (in SQL Server) based on selected rows in another table. not sure how to do this, and this SQL executes but updates all rows:

Code:

UPDATE cr_SampleDataSet_HeaderInfo SET LoginID = '', NetworkLogin= '', ReturnedFromField='Yes' SELECT * FROM qryOrigRUGItems;

doing WHERE (Select * from qryOrigRUGItems); spawns an error.

so, is this a syntax issue or am i pressing my luck?

Reply | Quote

Replies

WHERE (Select * from qryOrigRUGItems) makes no sense. What exactly do you want to do?

Reply | Quote

that should be obvious! i am trying to expand on ANSII SQL!

Well, what i am trying to do is run an UPDATE query that only writes to rows in one table that are identified in a separate query. i tried WHERE EXISTS and that got me a new error.

Reply | Quote

The WHERE clause has to specify some kind of condition on field(s) in the table you’re trying to update. Please try to provide specific information.

Reply | Quote

The WHERE clause has to specify some kind of condition on field(s) in the table you’re trying to update. Please try to provide specific information.

I am trying to update rows with specific info in Table A only if the rows in question are also in Query B. Several rows in Table A should *not* be updated.

Here’s an example from some website:

UPDATE suppliers
SET supplier_name = ( SELECT customers.name
FROM customers
WHERE customers.customer_id = suppliers.supplier_id)
WHERE EXISTS
( SELECT customers.name
FROM customers
WHERE customers.customer_id = suppliers.supplier_id);

on looking at it, it seems I need to declare the values in the update inside the select on the Query – I’ll try that next.

Reply | Quote

“if the rows in question are also in Query B” is very vague. We need specific information – in what sense are the rows also in Query B?

Reply | Quote

that should be obvious! i am trying to expand on ANSII SQL!

Well, what i am trying to do is run an UPDATE query that only writes to rows in one table that are identified in a separate query. i tried WHERE EXISTS and that got me a new error.

NOT OBVIOUS TO ME! I have no idea what qryOrigRUGItems is for a start.

[/size]
BUT anyway maybe something like[/size]

[/size]
WHERE (COUNT(*) from qryOrigRUGItems) 0[/size]

[/size]
Despite the fact the SQL and Access are written by the same company, they are as Heinz and Tesco’s Value Brand[/size]

[/size]

[/size]

Reply | Quote

Also, are you running this query from Access or in SQL Server? The syntax is different.

Reply | Quote

it’s dynamic SQL from Access to SQL Server using ADODB. I can get a straight UPDATE to work but it hits all rows, and i don’t want to do that.

Reply | Quote

I’m sorry if this isn’t clear. Table A has all the records, Query B is some subset of Table A. I want to update Table A for only those records that are in Query B with some predetermined values to certain columns in Table A.

Reply | Quote

If you can’t or won’t provide details I can’t help you.

Reply | Quote

If you can’t or won’t provide details I can’t help you.

I don’t know how I can state it much plainer. basically, instead of

INSERT INTO dbo_cr_OrigRUGItems SELECT * FROM qryOrigRUGItems

I want to UPDATE specific rows.

However, on thinking about it, I think I need to link the SQL table into Access first.

Reply | Quote

I’ll leave you in the able hands of Francois Caron…

Reply | Quote

Would’nt it be something like this :

UPDATE cr_SampleDataSet_HeaderInfo SET LoginID = ”, NetworkLogin= ”, ReturnedFromField=’Yes’ WHERE someIDfield IN (Select someIDfield from qryOrigRUGItems)

Reply | Quote

that’s what I just tried:

Code:

UPDATE dbo.cr_SampleDataSet_HeaderInfo SET LoginID ='', NetworkLogin='', ReturnedFromField='Yes' WHERE RecordID IN (SELECT RecordID FROM qryEditRUGItems);

I added the column RecordID and made sure the query has the same column (and data) – on run I get

Invalid object name ‘qryEditRUGitems’.

Now, the query definately exists, so not sure what the issue is. Perhaps ADODB is confused and things the query should be in SQL Server… Hmmm.

Reply | Quote

Are you sure about the query names ?
In your first post you mention qryOrigRUGItems and now qryEditRUGitems

Reply | Quote

Are you sure about the query names ?
In your first post you mention qryOrigRUGItems and now qryEditRUGitems

yes I’m sure. the correct query is the edit one. What i think i need to do is, dynamically link the table in question into Access rather than do something fancy like run UPDATE directly out of Access into SQL Server.

Reply | Quote

Problem mostly solved. I dynamically link the SQL Server table in and run UPDATE with the Access query providing the set of records to update. Much like Francois’s code. The issue was trying to combine data from SQL Server and Access in one command.

Reply | Quote