<div>
As you have only one row in our destination table (hope you ensured that), its:<br />
<br />

<pre><code class="code-1 nolinks" id="code-20-42347884-1"><span>UPDATE ABCD</span>
<span>SET AAA = ( </span>
<span>  SELECT TOP 1 [TimeRange].BankBal</span>
<span>  FROM [TimeRange]</span>
<span>);</span>
</code></pre>
<br />
But without ORDER BY you'll get an arbitrary value from TimeRange.
</div>


<div>
You can update like below..<br />
<br />
Update x<br />
Set x.aaa = t.Bankbal<br />
From abcd x inner join TimeRange t<br />
On x.keyfield = t.keyfield
</div>


<div>
Dear Pawan,<br />
No Common fields to join on.<br />
<br />
Dear Ste5an,<br />
I will try and let you know soon.<br />
<br />
Thanks
</div>


<div>
You can do like below<br />
<br />
Update x<br />
Set x.aaa = t.Bankbal<br />
From abcd x inner join TimeRange t<br />
On 1=1
</div>


<div>
Dear Ste5an,,<br />
<br />
I did your suggestion but when Running, it gives an error saying: &nbsp; &nbsp;<i><b>'Operation must use an updateable query'</b></i>.<br />
I don't understand more of this as it doesn't much more information.<br />
:(
</div>


<div>
Pls try my last suggestion.
</div>


<div>
We can also do like below -<br />
<br />
<b>DataType you can choose based on what you have.</b><br />
<br />

<pre><code class="code-10 nolinks" id="code-20-42347983-1"><span>--</span>
<span></span>
<span>DECLARE @AAA AS DECIMAL(30,2) </span>
<span></span>
<span>SELECT TOP 1 @AAA = [TimeRange].BankBal FROM [TimeRange]</span>
<span></span>
<span>UPDATE ABCD</span>
<span>SET AAA = @AAA</span>
<span></span>
<span>--</span>
</code></pre>
</div>


<div>
This will work:<br />

<pre><code class="code-1 nolinks" id="code-20-42348038-1"><span>update a</span>
<span>set a.AAA = b.BankBal</span>
<span>from</span>
<span>abcd a</span>
<span>cross join (select top 1 bankbal from timerange) b;</span>
<span></span>
<span>select * from abcd</span>
</code></pre>
<br />
Here's the sql Fiddle: <a href="http://sqlfiddle.com/#!6/a9779/3/0" rel="ugc">http://sqlfiddle.com/#!6/a9779/3/0</a>
</div>


<div>
Just a note if you're running it in access you have to do the select first.<br />
<br />
FROM:<br />
<a href="https://www.techonthenet.com/access/queries/update2_2007.php" rel="ugc">https://www.techonthenet.com/access/queries/update2_2007.php</a><br />

<pre><code class="code-1 nolinks" id="code-20-42348048-1"><span>UPDATE abcd cross join (select top 1 bankbal from timerange) as TimeRange</span>
<span>set ABCD.AAA = TimeRange.BankBal</span>
</code></pre>
</div>


<div>
Use <i>DLookup</i>. Plain and easy:<br />
<br />

<pre><code class="code-1 nolinks" id="code-20-42348135-1"><span>Update ABCD</span>
<span>Set AAA = DLookup(&quot;[BankBal]&quot;,&quot;[TimeRange]&quot;);</span>
</code></pre>
/gustav
</div>


<div>
Dear Gust,,<br />
<br />
How to make sure that I am picking from the top record of the TimeRange ?<br />
I tried it but it is picking from the last record!!!<br />
Thanks
</div>


<div>
Neither did TOP 1 do that on its own.<br />
<br />
But you can use:<br />
<br />

<pre><code class="code-1 nolinks" id="code-20-42348292-1"><span>Update ABCD</span>
<span>Set AAA = DLookup(&quot;[BankBal]&quot;,&quot;[TimeRange]&quot;,&quot;[DateField] = (Select Max(DateField) From TimeRange)&quot;);</span>
</code></pre>
/gustav
</div>


<div>
Dear Gustav:<br />
My real test Statement is:<br />
<br />
Update [Copy of Sanad cushist Record]<br />
Set retvalue = DLookup(&quot;[BankBal]&quot;,&quot; [Bashitee=Statement Of Account-TimeRange] &quot;);<br />
<br />
What am after is to update the Retvalue in Copy of [Copy of Sanad cushist Record] &nbsp; with the [BankBal] in the First record in Table &nbsp;[Bashitee=Statement Of Account-TimeRange].<br />
<br />
When I run it, it gives error &nbsp;'Unkown' and points to [RetValue] &nbsp; &nbsp; !!!!? &nbsp; <br />
Thanks again
</div>


<div>
You can do like below - Below will pick the latest record.<br />
<br />
Access-<br />
<br />
UPDATE abcd cross join (select top 1 bankbal from timerange <b>ORDER BY YourDateColumn DESC </b>) as TimeRange<br />
set ABCD.AAA = TimeRange.BankBal<br />
<br />
or <br />
<br />
For SQL Server <br />
<br />
DECLARE @AAA AS DECIMAL(30,2) <br />
<br />
SELECT TOP 1 @AAA = [TimeRange].BankBal<b> &nbsp;ORDER BY yourDateColumn DESC </b>FROM [TimeRange]<br />
<br />
UPDATE ABCD<br />
SET AAA = @AAA
</div>


Capture22.PNG

<div>
A bit confusing with all those field and/or table names, but it might read:<br />
<br />

<pre><code class="code-1 nolinks" id="code-20-42348317-1"><span>Update [Copy of Sanad cushist Record] </span>
<span>Set Retvalue = DLookup(&quot;[BankBal]&quot;,&quot;[Statement Of Account-TimeRange]&quot;,&quot;[Bashitee] = (Select Max([Bashitee]) From [Statement Of Account-TimeRange])&quot;);</span>
</code></pre>
/gustav
</div>


<div>
Dear Gust,,<br />
<br />
The same error is still showing : &nbsp;  'Unkown' and points to [RetValue]'
</div>


<div>
That is the field name, so an error means that it either is non existing or misspelled.<br />
<br />
/gustav
</div>


<div>
Dear Gust,,,<br />
<br />
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.<br />
The same error appears when I RUN it.<br />
I will send you screen shots in a minutes.<br />
Thanks for your patience.
</div>


<div>
It is what you originally called &quot;field AAA &nbsp;in table ABCD&quot;.<br />
<br />
/gustav
</div>


<div>
<b>This the Datasheet &nbsp;view</b><br />
<a href="https://filedb.experts-exchange.com/incoming/2017/10_w44/1202113/Capture22-a.PNG" target="_blank" title="DataSheet view (7 KB)"><img alt="DataSheet view" class="file-block lazyload" style="max-width: 463px;" data-src="https://filedb.experts-exchange.com/incoming/2017/10_w44/1202113/Capture22-a.PNG" /></a><br />
<b>This is the RUN error</b><br />
<a href="https://filedb.experts-exchange.com/incoming/2017/10_w44/1202114/Capture22-b.PNG" target="_blank" title="The RUN  error (76 KB)"><img alt="The RUN  error" class="file-block lazyload" style="max-width: 800px;" data-src="https://filedb.experts-exchange.com/incoming/2017/10_w44/800_1202114/Capture22-b.PNG" /></a>
</div>


Capture22-a.PNG

Capture22-b.PNG

<div>
Yes like this:<br />
<br />
&quot;The field in the second table to be updated , say  AAA  in table ABCD.&quot;<br />
<br />
Also,<br />
This is my bare select before converting it to the Update, &nbsp; which is still picking the correct value.<br />
&nbsp; &nbsp; &nbsp; SELECT TOP 1 [TimeRange].BankBal<br />
FROM [TimeRange];
</div>


<div>
First, check that you get a value from DLookup. Press Ctrl+G and enter:<br />
<br />

<pre><code class="code-1 nolinks" id="code-20-42348611-1"><span>? DLookup(&quot;[BankBal]&quot;,&quot;[Statement Of Account-TimeRange]&quot;,&quot;[Bashitee] = (Select Max([Bashitee]) From [Statement Of Account-TimeRange])&quot;)</span>
</code></pre>
Correct if needed.<br />
<br />
Next, switch the query to SQL view and double-check that the code looks similar to what I posted above:<br />
<a href="https://www.experts-exchange.com/questions/29065359/SQL-to-UPDATE-one-field-in-another-field-in-a-Table-which-has-only-one-Record.html?anchorAnswerId=42348317#a42348317" rel="ugc">https://www.experts-exchange.com/questions/29065359/SQL-to-UPDATE-one-field-in-another-field-in-a-Table-which-has-only-one-Record.html?anchorAnswerId=42348317#a42348317</a><br />
<br />
/gustav
</div>


<div>
Well, then:<br />
<br />

<pre><code class="code-1 nolinks" id="code-20-42348619-1"><span>? DLookup(&quot;[BankBal]&quot;,&quot;[TimeRange]&quot;,&quot;[Bashitee] = (Select Max([Bashitee]) From [TimeRange])&quot;)</span>
</code></pre>
<br />
and:<br />
<br />

<pre><code class="code-1 nolinks" id="code-20-42348619-2"><span>Update [Copy of Sanad cushist Record] </span>
<span>Set Retvalue = DLookup(&quot;[BankBal]&quot;,&quot;[TimeRange]&quot;,&quot;[Bashitee] = (Select Max([Bashitee]) From [TimeRange])&quot;);</span>
</code></pre>
/gustav
</div>


<div>
The Real Sorry the above is a simulated SQL.<br />
The real one is :<br />
<i><b>SELECT TOP 1 [Bashitee=Statement Of Account-TimeRange]![BankBa<wbr />l] AS RetValue<br />
FROM [Bashitee=Statement Of Account-TimeRange];</b></i><br />
<br />
<br />
The result of the Select Query is :<br />
<a href="https://filedb.experts-exchange.com/incoming/2017/10_w44/1202119/Capture22-c.PNG" target="_blank" title="Capture22-c.PNG (9 KB)"><img alt="Capture22-c.PNG" class="file-block lazyload" style="max-width: 510px;" data-src="https://filedb.experts-exchange.com/incoming/2017/10_w44/1202119/Capture22-c.PNG" /></a>Which is also picking the correct value that should replace the 333 value in the above 1st screen shot.
</div>


Capture22-c.PNG

<div>
Dear Gusta,,,<br />
<br />
hmmm &nbsp; still the same &nbsp;!<br />
<a href="https://filedb.experts-exchange.com/incoming/2017/10_w44/1202129/Capture-d.PNG" target="_blank" title="last error (85 KB)"><img alt="last error" class="file-block lazyload" style="max-width: 800px;" data-src="https://filedb.experts-exchange.com/incoming/2017/10_w44/800_1202129/Capture-d.PNG" /></a>
</div>


Capture-d.PNG

<div>
am &nbsp;sorry for taking much of your time but it is really &nbsp;getting on my serves.
</div>


<div>
Then debug again as explained here:<br />
<br />
<a href="https://www.experts-exchange.com/questions/29065359/SQL-to-UPDATE-one-field-in-another-field-in-a-Table-which-has-only-one-Record.html?anchorAnswerId=42348619#a42348619" rel="ugc">https://www.experts-exchange.com/questions/29065359/SQL-to-UPDATE-one-field-in-another-field-in-a-Table-which-has-only-one-Record.html?anchorAnswerId=42348619#a42348619</a><br />
<br />
And do double-check your weird table names.<br />
<br />
/gustav
</div>


<div>
OK, sure<br />
I will also prepare a small isolated DB and may pass it to you.
</div>


<div>
Dear Gustav,,,<br />
Wonderfulllllllllll Now working.<br />
I am so sorry for the above hassle due to being under pressure.<br />
My SQL now is:<br />
<br />
<b>UPDATE [Sanad Record] SET [Sanad Record].Retvalue = DLookUp(&quot;[BankBal]&quot;,&quot;[Time<wbr />Range]&quot;,&quot;[<wbr />Date] = (Select Min([date]) From [TimeRange])&quot;);<br />
<br />
</b>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.<br />
<br />
Tons of thanks
</div>


<div>
Great! Then please mark as answered.<br />
<br />
/gustav
</div>


<div>
Closing - Accepting verified solution by Author
</div>


<div>
<div class="content wysiwyg-content">
Hi All,,<br />
I made this SQL to pick up one value from the top row of file.<br />
How cab I convert this on value result of this SQL to UPDATE one field in another field in a Table which has only <b>one</b> Record.<br />
The field in the second table to be updated , say &nbsp;AAA &nbsp;in table ABCD.<br />
<br />
<b>SELECT TOP 1 [TimeRange].BankBal<br />
FROM [TimeRange];</b><br />
<br />
The above select is picking the correct value that I need.<br />
<br />
Optionally, Can I send the value of this SQL &nbsp;into a bound object (or even unbound) in an open form?<br />
<br />
Thanks
</div>
</div>


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

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

Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.

Microsoft Access

SQL (Structured Query Language) is designed to be used in conjunction with relational database products as of a means of working with sets of data. SQL consists of data definition, data manipulation, and procedural elements. Its scope includes data insert, query, update and delete, schema creation and modification, and data access control.