Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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 = '<loginvalue>', NetworkLogin= '<loginvalue>', 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?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    WHERE (Select * from qryOrigRUGItems) makes no sense. What exactly do you want to do?

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    Also, are you running this query from Access or in SQL Server? The syntax is different.
    Wendell

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    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.

  6. #6
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.

  7. #7
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    Quote Originally Posted by steve_skelton13 View Post
    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.


    BUT anyway maybe something like


    WHERE (COUNT(*) from qryOrigRUGItems) <> 0


    Despite the fact the SQL and Access are written by the same company, they are as Heinz and Tesco's Value Brand



    Andrew

  8. #8
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by HansV View Post
    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.

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    "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?

  10. #10
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    If you can't or won't provide details I can't help you.

  12. #12
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Would'nt it be something like this :

    UPDATE cr_SampleDataSet_HeaderInfo SET LoginID = '<loginvalue>', NetworkLogin= '<loginvalue>', ReturnedFromField='Yes' WHERE someIDfield IN (Select someIDfield from qryOrigRUGItems)
    Francois

  13. #13
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts
    that's what I just tried:

    Code:
    UPDATE dbo.cr_SampleDataSet_HeaderInfo SET LoginID ='<login>', NetworkLogin='<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.

  14. #14
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Are you sure about the query names ?
    In your first post you mention qryOrigRUGItems and now qryEditRUGitems
    Francois

  15. #15
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by HansV View Post
    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.

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •