SQL Update based on Select Statement

update "table one"
set "field one"=(select value from "Table Two" where "Field 10" = '31'
And "Field 15" > '11/15/2011')


you have * in place of value this wont' work. you need a single column.

also you should do an update as an inner join as that will run faster than a subquery. if you can give some sample data could help you better.

the logic should be like:

UPDATE t1 SET  t1.col1 = t2.col1,
t1.col2 = t2.col2
FROM table2 t2 
join table1 t1 on t1.id = t2.id
WHERE t1=2

Select allOpen in new window

Are you trying to update one column or all columns that are in the select statement?

In the update command looks like you are updating just one field but with the select statement you are selecting multiple columns?

Just trying to update 1 column with a specific value, but I want it based on the results of my select statement

What results? What do you want it based on?

You can't update one column with a select statement that selects multiple columns.

medpipes - is there a common column or link between the tables that you want to base the update on ?

if there is no link then use the query that i provided else. do an inner join like

update "table one"
set "field one"=value
from "Table Two"
join "table one".columntobejoined="table two".columnjoined
where "Field 15" > '11/15/2011'

replace columnstobejoined and columnjoined with the relevant columns

Read this article. It will give you a clear vision:-

https://www.experts-exchange.com/Database/Miscellaneous/A_1517-UPDATES-with-JOIN-for-everybody.html

There are 2 tables of information in the database: Table 1 and Table 2.  They do have a common field "Billing".  So you could think of it as a table of Invoices.  Table 1 contains header info like Name, Entry Date, etc... and table 2 contains the line items of the invoice.  I want to update a specific field in table 1 based on a query of table 2 - that is I don't want to update field 1 for every record in table 1, just for those that meet the criteria of my query on table 2.

Say I want to reprint invoices for people who bought sweaters after 11/15/11.  So I have a field in table 1 to tag an invoice for reprint, i.e. "1" = print,  so therefore:

Update "Billing Header"
Set Print = '1'

But I don't want to reprint everybody's invoice just certain ones...

Select * from "Billing Detail"
Where Item = 'sweater'
AND date > '11/15/11'

Both the Billing Header table and the Billing Detail table have a common field "Billing" which is the invoice number.

How do I combine this statement to get the Print field updated for specific invoices?

Thank for helping me.