SQL to UPDATE one field in another field in a Table which has only one Record

Issa S.
Issa S. used Ask the Experts™
on
Hi All,,
I made this SQL to pick up one value from the top row of file.
How cab I convert this on value result of this SQL to UPDATE one field in another field in a Table which has only one Record.
The field in the second table to be updated , say  AAA  in table ABCD.

SELECT TOP 1 [TimeRange].BankBal
FROM [TimeRange];


The above select is picking the correct value that I need.

Optionally, Can I send the value of this SQL  into a bound object (or even unbound) in an open form?

Thanks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
ste5anSenior Developer

Commented:
As you have only one row in our destination table (hope you ensured that), its:

UPDATE ABCD
SET AAA = ( 
  SELECT TOP 1 [TimeRange].BankBal
  FROM [TimeRange]
);

Open in new window


But without ORDER BY you'll get an arbitrary value from TimeRange.
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
You can update like below..

Update x
Set x.aaa = t.Bankbal
From abcd x inner join TimeRange t
On x.keyfield = t.keyfield

Author

Commented:
Dear Pawan,
No Common fields to join on.

Dear Ste5an,
I will try and let you know soon.

Thanks
11/26 Forrester Webinar: Savings for Enterprise

How can your organization benefit from savings just by replacing your legacy backup solutions with Acronis' #CyberProtection? Join Forrester's Joe Branca and Ryan Davis from Acronis live as they explain how you can too.

Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
You can do like below

Update x
Set x.aaa = t.Bankbal
From abcd x inner join TimeRange t
On 1=1

Author

Commented:
Dear Ste5an,,

I did your suggestion but when Running, it gives an error saying:    'Operation must use an updateable query'.
I don't understand more of this as it doesn't much more information.
:(
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
Pls try my last suggestion.
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
We can also do like below -

DataType you can choose based on what you have.

--

DECLARE @AAA AS DECIMAL(30,2) 

SELECT TOP 1 @AAA = [TimeRange].BankBal FROM [TimeRange]

UPDATE ABCD
SET AAA = @AAA

--

Open in new window

Kyle AbrahamsSenior .Net Developer

Commented:
This will work:
update a
set a.AAA = b.BankBal
from
abcd a
cross join (select top 1 bankbal from timerange) b;

select * from abcd

Open in new window


Here's the sql Fiddle: http://sqlfiddle.com/#!6/a9779/3/0
Kyle AbrahamsSenior .Net Developer

Commented:
Just a note if you're running it in access you have to do the select first.

FROM:
https://www.techonthenet.com/access/queries/update2_2007.php
UPDATE abcd cross join (select top 1 bankbal from timerange) as TimeRange
set ABCD.AAA = TimeRange.BankBal

Open in new window

Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
Use DLookup. Plain and easy:

Update ABCD
Set AAA = DLookup("[BankBal]","[TimeRange]");

Open in new window

/gustav

Author

Commented:
Dear Gust,,

How to make sure that I am picking from the top record of the TimeRange ?
I tried it but it is picking from the last record!!!
Thanks
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
Neither did TOP 1 do that on its own.

But you can use:

Update ABCD
Set AAA = DLookup("[BankBal]","[TimeRange]","[DateField] = (Select Max(DateField) From TimeRange)");

Open in new window

/gustav

Author

Commented:
Dear Gustav:
My real test Statement is:

Update [Copy of Sanad cushist Record]
Set retvalue = DLookup("[BankBal]"," [Bashitee=Statement Of Account-TimeRange] ");

What am after is to update the Retvalue in Copy of [Copy of Sanad cushist Record]   with the [BankBal] in the First record in Table  [Bashitee=Statement Of Account-TimeRange].

When I run it, it gives error  'Unkown' and points to [RetValue]     !!!!?  
Thanks again
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
You can do like below - Below will pick the latest record.

Access-

UPDATE abcd cross join (select top 1 bankbal from timerange ORDER BY YourDateColumn DESC ) as TimeRange
set ABCD.AAA = TimeRange.BankBal

or

For SQL Server

DECLARE @AAA AS DECIMAL(30,2)

SELECT TOP 1 @AAA = [TimeRange].BankBal  ORDER BY yourDateColumn DESC FROM [TimeRange]

UPDATE ABCD
SET AAA = @AAA

Author

Commented:
Design view
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
A bit confusing with all those field and/or table names, but it might read:

Update [Copy of Sanad cushist Record] 
Set Retvalue = DLookup("[BankBal]","[Statement Of Account-TimeRange]","[Bashitee] = (Select Max([Bashitee]) From [Statement Of Account-TimeRange])");

Open in new window

/gustav

Author

Commented:
Dear Gust,,

The same error is still showing :    'Unkown' and points to [RetValue]'
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
That is the field name, so an error means that it either is non existing or misspelled.

/gustav

Author

Commented:
Dear Gust,,,

I checked and re-check. It is the correct name. To make sure it is the right field, I run it in the Datasheet view and it shows the correct value in the destination field that has to be replaced with the new value.
The same error appears when I RUN it.
I will send you screen shots in a minutes.
Thanks for your patience.
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
It is what you originally called "field AAA  in table ABCD".

/gustav

Author

Commented:
This the Datasheet  view
DataSheet view
This is the RUN error
The RUN  error

Author

Commented:
Yes like this:

"The field in the second table to be updated , say  AAA  in table ABCD."

Also,
This is my bare select before converting it to the Update,   which is still picking the correct value.
      SELECT TOP 1 [TimeRange].BankBal
FROM [TimeRange];
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
First, check that you get a value from DLookup. Press Ctrl+G and enter:

? DLookup("[BankBal]","[Statement Of Account-TimeRange]","[Bashitee] = (Select Max([Bashitee]) From [Statement Of Account-TimeRange])")

Open in new window

Correct if needed.

Next, switch the query to SQL view and double-check that the code looks similar to what I posted above:
https://www.experts-exchange.com/questions/29065359/SQL-to-UPDATE-one-field-in-another-field-in-a-Table-which-has-only-one-Record.html#a42348317

/gustav
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
Well, then:

? DLookup("[BankBal]","[TimeRange]","[Bashitee] = (Select Max([Bashitee]) From [TimeRange])")

Open in new window


and:

Update [Copy of Sanad cushist Record] 
Set Retvalue = DLookup("[BankBal]","[TimeRange]","[Bashitee] = (Select Max([Bashitee]) From [TimeRange])");

Open in new window

/gustav

Author

Commented:
The Real Sorry the above is a simulated SQL.
The real one is :
SELECT TOP 1 [Bashitee=Statement Of Account-TimeRange]![BankBal] AS RetValue
FROM [Bashitee=Statement Of Account-TimeRange];



The result of the Select Query is :
Capture22-c.PNGWhich is also picking the correct value that should replace the 333 value in the above 1st screen shot.
Most Valuable Expert 2015
Distinguished Expert 2018
Commented:
You really should avoid such totally confusing table names.
Anyway, it might be this (adjust field name):

Update [Copy of Sanad cushist Record] 
Set Retvalue = DLookup("[BankBal]","[Bashitee=Statement Of Account-TimeRange]","[ReplaceWithYourDateFieldName] = (Select Max([ReplaceWithYourDateFieldName]) From [Bashitee=Statement Of Account-TimeRange])");

Open in new window

/gustav

Author

Commented:
Dear Gusta,,,

hmmm   still the same  !
last error

Author

Commented:
am  sorry for taking much of your time but it is really  getting on my serves.
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:

Author

Commented:
OK, sure
I will also prepare a small isolated DB and may pass it to you.

Author

Commented:
Dear Gustav,,,
Wonderfulllllllllll Now working.
I am so sorry for the above hassle due to being under pressure.
My SQL now is:

UPDATE [Sanad Record] SET [Sanad Record].Retvalue = DLookUp("[BankBal]","[TimeRange]","[Date] = (Select Min([date]) From [TimeRange])");

as in my simulated DB that I was about to send to. I only changed the MAX to MIN since my dates are in ascending order.

Tons of thanks
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
Great! Then please mark as answered.

/gustav
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
Closing - Accepting verified solution by Author

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial