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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Issa S.Author Commented:
Dear Pawan,
No Common fields to join on.

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

Thanks
0
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.