CODEWITHSUNDEEP
sqlms-access
Vomish
Vomish

Reputation: 13

Is UPDATE without a JOIN possible?

I am trying to update a single column (all rows) from one table with a single value from another table. The problem is there is no index fields to join the tables on. Here is an example of the tables/columns in question (without data):

Table1: ID, Name, Address, Telephone, PriceList
Table2: PriceList, Description

I want to update Table1.Pricelist with the value in Table2.Pricelist

The current data I am testing with has one row in Table2 but it is possible for there to be more. In that case, I would just use the first value returned.

I thought I would post here to get the definitive answer as to whether this is possible.

Upvotes: 1

Views: 3571

Answers (2)

Joe Love
Joe Love

Reputation: 5962

update table1
set pricelist= (select top(1) table2.pricelist from table2);

Upvotes: 2

HansUp
HansUp

Reputation: 97101

UPDATE Table1
SET Pricelist = DLookup("PriceList", "Table2");

The DLookup expression will return a single value from Table2. So when Table2 contains only one row, it will give you the "first" PriceList value. However, with more rows in Table2, DLookup will still return one value but that may not come from the row which you consider to be the "first" row.

It would help to know how to identify which target row contains the PriceList value you want to use in the UPDATE.

Upvotes: 0

Related Questions