Use Select Inside an Update Query

Sub Select Inside UPDATE

Try this

UPDATE guilds AS g 
SET g.primarycolour = (
    SELECT c.colour FROM table_name AS c 
    WHERE c.id = 1
), g.secondarycolour = (
    SELECT c.colour FROM table_name AS c
    WHERE c.id = 2
)
WHERE g.id = 1

Note: I didn't test it though.

Use SELECT inside an UPDATE query

Well, it looks like Access can't do aggregates in UPDATE queries. But it can do aggregates in SELECT queries. So create a query with a definition like:

SELECT func_id, min(tax_code) as MinOfTax_Code
FROM Functions
INNER JOIN Tax 
ON (Functions.Func_Year = Tax.Tax_Year) 
AND (Functions.Func_Pure <= Tax.Tax_ToPrice) 
GROUP BY Func_Id

And save it as YourQuery. Now we have to work around another Access restriction. UPDATE queries can't operate on queries, but they can operate on multiple tables. So let's turn the query into a table with a Make Table query:

SELECT YourQuery.* 
INTO MinOfTax_Code
FROM YourQuery

This stores the content of the view in a table called MinOfTax_Code. Now you can do an UPDATE query:

UPDATE MinOfTax_Code 
INNER JOIN Functions ON MinOfTax_Code.func_id = Functions.Func_ID 
SET Functions.Func_TaxRef = [MinOfTax_Code].[MinOfTax_Code]

Doing SQL in Access is a bit of a stretch, I'd look into Sql Server Express Edition for your project!

SELECT inside an UPDATE query

You need to write your SELECT as an assignment from a subquery (enclosed in parentheses), and also specify the table you are selecting the value from:

UPDATE MEMBER 
SET FIRSTNAME=?, 
    LASTNAME=?, 
    STREETADDRESS=?, 
    CITY=?, 
    STATE=?, 
    ZIP=?,
    PHONE=?,
    MEMBERSHIPID = (SELECT MEMBERSHIPID 
                    FROM MEMBERSHIP 
                    WHERE MEMNAME=?)
WHERE MEMBERID=?

How do I UPDATE from a SELECT in SQL Server?

UPDATE
    Table_A
SET
    Table_A.col1 = Table_B.col1,
    Table_A.col2 = Table_B.col2
FROM
    Some_Table AS Table_A
    INNER JOIN Other_Table AS Table_B
        ON Table_A.id = Table_B.id
WHERE
    Table_A.col3 = 'cool'

MySQL query - Use SELECT inside an UPDATE

First, when using LIMIT and OFFSET you need to use ORDER BY as well. Otherwise the row you get is indeterminate.

One method uses LIMIT within the UPDATE itself. However, UPDATE doesn't allow OFFSET. So:

UPDATE table 
    SET value = 1 
    WHERE some criteria
    ORDER BY ??
    LIMIT 1;

The best method would use a unique id. You can do this with the double subquery approach:

UPDATE table 
    SET value = 1 
    WHERE id IN (SELECT id
                 FROM (SELECT id
                       FROM table
                       WHERE some criteria
                       ORDER BY ??
                       LIMIT 1 OFFSET 2
                      ) t
                );

If you don't have a single unique id, you can use multiple columns that uniquely define a single row.

How to SELECT inside UPDATE in a MYSQL query

Because you did not provide a simple test data Create Table/Insert Into (copypaste text) script I had to experiment with my own table scheme. See this with aTemp temporary table how I use two-level inner table.

Update person Set 
city=(
 Select Concat(now(),"-",aTemp.val1) from
 (select count(*) as val1 from person) as aTemp
)
Where id=4

So out of my mind it may translate to this query.

UPDATE usersDB.random
SET total_partners=(
 Select aTemp.val1 From
 (SELECT count(*) as val1 FROM usersDB.random WHERE year=2022) aTemp
)
WHERE uid = 1

Updating a table within a select statement

If your goal is to update tbl2 every time you query tbl1, then the best way to do that is to create a stored procedure to do it and wrap it in a transaction, possibly changing isolation levels if atomicity is needed.

You can't nest updates in selects.

MySQL - UPDATE query based on SELECT Query

You can actually do this one of two ways:

MySQL update join syntax:

UPDATE tableA a
INNER JOIN tableB b ON a.name_a = b.name_b
SET validation_check = if(start_dts > end_dts, 'VALID', '')
-- where clause can go here

ANSI SQL syntax:

UPDATE tableA SET validation_check = 
    (SELECT if(start_DTS > end_DTS, 'VALID', '') AS validation_check
        FROM tableA
        INNER JOIN tableB ON name_A = name_B
        WHERE id_A = tableA.id_A)

Pick whichever one seems most natural to you.

Mysql SELECT inside UPDATE

Consp is right that it's not supported. There's a workaround, however:

UPDATE forms SET
pos = (SELECT MIN(pos)-1 FROM (SELECT * FROM forms) AS x)
WHERE id=$id

A version that is probably faster:

UPDATE forms 
SET pos = (SELECT pos-1 FROM (SELECT MIN(pos) AS pos FROM forms) AS x)
where id=$id

Update with Select in Microsoft Access

First, MS Access uses UPDATE...JOIN as its syntax and not UPDATE...FROM for multiple tables in UPDATE queries. Second, Access requires update queries to be updateable where self-joins or subqueries are not updateable.

However, you can use domain function, DLookUp, to retrieve values from same table:

UPDATE [Table] t
SET t.[Name] = DLookUp("[Name]", "[Table]", 
                       "[Name] <> 'NoName' AND [Ref] = '" & t.[Ref] & "'")

Note: this solution will only work if using query inside MSAccess.exe (Office program) and not its backend database via ODBC/OLEDB. Domain functions are part of hte Access object library and not the Jet/ACE SQL Engine. And Ref is assumed to be a string value.

---

Related Topics