We help IT Professionals succeed at work.

SQL Update based on Select Statement

medpipes
medpipes asked
on
I am trying to write a simple Update query that is based on a select statement involving a 2nd table and for some reason I keep getting an error:

Update "Table One"
Set "Field One" = 'x'

and I want this to be based on the results of a select statement from another table such as
Select * from "Table Two" where "Field 10" = '31'
And "Field 15" > '11/15/2011'

I think I'm just missing some logic in combining them because the select statement seems to run fine on its own.
Comment
Watch Question

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

Open in new window

Top Expert 2012

Commented:
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?

Author

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

Commented:
What results? What do you want it based on?

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

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

Author

Commented:
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.

Author

Commented:
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?
Commented:
update "Billing Header"
set Print='1'
from "Billing Details"
where "Billing Header".billing="Billing Details".billing
and "Billing Details".item='sweater'
and "Billing Details".date > '11/15/11'

Author

Commented:
Thank for helping me.