asked on

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

ste5an

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 Kumar

You can update like below..

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

Issa S.

ASKER

Dear Pawan,
No Common fields to join on.

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

Thanks

Pawan Kumar

You can do like below

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

Issa S.

ASKER

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 Kumar

Pls try my last suggestion.

Pawan Kumar

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 Abrahams, PMP

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 Abrahams, PMP

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

Gustav Brock

Use DLookup. Plain and easy:

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

Open in new window

/gustav

Issa S.

ASKER

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

Gustav Brock

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

Issa S.

ASKER

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 Kumar

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

Issa S.

ASKER

Gustav Brock

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

Issa S.

ASKER

Dear Gust,,

The same error is still showing : 'Unkown' and points to [RetValue]'

Gustav Brock

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

/gustav

Issa S.

ASKER

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.

Gustav Brock

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

/gustav

Issa S.

ASKER

This the Datasheet view

This is the RUN error

Issa S.

ASKER

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

Gustav Brock

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?anchorAnswerId=42348317#a42348317

/gustav

Gustav Brock

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

Issa S.

ASKER

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 :

Which is also picking the correct value that should replace the 333 value in the above 1st screen shot.

ASKER CERTIFIED SOLUTION

Gustav Brock

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Issa S.

ASKER

Dear Gusta,,,

hmmm still the same !

Issa S.

ASKER

am sorry for taking much of your time but it is really getting on my serves.

Gustav Brock

Then debug again as explained here:

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

And do double-check your weird table names.

/gustav

Issa S.

ASKER

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

Issa S.

ASKER

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

Gustav Brock

Great! Then please mark as answered.

/gustav

Pawan Kumar

Closing - Accepting verified solution by Author