CODEWITHSUNDEEP
sqlsql-update
AceAlfred
AceAlfred

Reputation: 1191

SQL - Update to table, no index

I have two tables: DIM_Staff and FACT_Sales

I want to update [e-mail] to FACT_Sales from DIM_Staff.

The only way to link the [e-mail] is through DIM_Staff.[Name] and FACT_Sales.[Name] since there is no social security number.

I have already gone through both tables to check for misspelling.

This is what I have tried so far:

UPDATE [vdb].[dbo].[FACT_Sales] 
SET [e-mail] = (SELECT [e-mail] FROM DIM_Staff WHERE [Name] = [dbo].[FACT_Sales].[Name])


UPDATE [vdb].[dbo].[FACT_Sales] 
SET [e-mail] = (SELECT [e-mail] FROM DIM_Staff WHERE [Name] in (SELECT [Name] FROM [dbo].[FACT_Sales])

Error message:

Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , > >, >= or when the subquery is used as an expression. The statement has been terminated.

It should be a pretty basic update?

Upvotes: 0

Views: 318

Answers (3)

Vikram
Vikram

Reputation: 8333

try the follwoing

UPDATE t
SET t.[e-mail] = t2.[e-mail]
from [vdb].[dbo].[FACT_Sales] t
inner join DIM_Staff t2
on t.Name = t2.Name

Upvotes: 2

Omar Qureshi
Omar Qureshi

Reputation: 9093

So, you can't do that query because it would be akin to doing

set email = val1, val2, val3

which is not valid

I'm not sure what dbms that is, but in PostgreSQL and MySQL you should be able to do an update over an inner join, that way, all rows that don't match the condition are excluded and you can do the update simply that way.

Start it off with a select query to get the values you want then change it into an update statement.

Upvotes: 0

Nikola Markovinović
Nikola Markovinović

Reputation: 19356

It is, but you have people in DIM_Staff sharing the same name. You need to resolve that first.

Upvotes: 0

Related Questions