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

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
Issa S.Asked:
Who is Participating?
 
Gustav BrockCIOCommented:
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
0
 
ste5anSenior DeveloperCommented:
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.
0
 
Pawan KumarDatabase ExpertCommented:
You can update like below..

Update x
Set x.aaa = t.Bankbal
From abcd x inner join TimeRange t
On x.keyfield = t.keyfield
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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

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

Thanks
0
 
Pawan KumarDatabase ExpertCommented:
You can do like below

Update x
Set x.aaa = t.Bankbal
From abcd x inner join TimeRange t
On 1=1
0
 
Issa S.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.
:(
0
 
Pawan KumarDatabase ExpertCommented:
Pls try my last suggestion.
0
 
Pawan KumarDatabase ExpertCommented:
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

0
 
Kyle AbrahamsSenior .Net DeveloperCommented:
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
0
 
Kyle AbrahamsSenior .Net DeveloperCommented:
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

0
 
Gustav BrockCIOCommented:
Use DLookup. Plain and easy:

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

Open in new window

/gustav
0
 
Issa S.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
0
 
Gustav BrockCIOCommented:
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
0
 
Issa S.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
0
 
Pawan KumarDatabase ExpertCommented:
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
0
 
Issa S.Author Commented:
Design view
0
 
Gustav BrockCIOCommented:
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
0
 
Issa S.Author Commented:
Dear Gust,,

The same error is still showing :    'Unkown' and points to [RetValue]'
0
 
Gustav BrockCIOCommented:
That is the field name, so an error means that it either is non existing or misspelled.

/gustav
0
 
Issa S.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.
0
 
Gustav BrockCIOCommented:
It is what you originally called "field AAA  in table ABCD".

/gustav
0
 
Issa S.Author Commented:
This the Datasheet  view
DataSheet view
This is the RUN error
The RUN  error
0
 
Issa S.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];
0
 
Gustav BrockCIOCommented:
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
0
 
Gustav BrockCIOCommented:
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
0
 
Issa S.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.
0
 
Issa S.Author Commented:
Dear Gusta,,,

hmmm   still the same  !
last error
0
 
Issa S.Author Commented:
am  sorry for taking much of your time but it is really  getting on my serves.
0
 
Gustav BrockCIOCommented:
1
 
Issa S.Author Commented:
OK, sure
I will also prepare a small isolated DB and may pass it to you.
0
 
Issa S.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
1
 
Gustav BrockCIOCommented:
Great! Then please mark as answered.

/gustav
1
 
Pawan KumarDatabase ExpertCommented:
Closing - Accepting verified solution by Author
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.