May 22, 2008 at 3:10 pm
I have a question on using a join condition when updating a table. I guess it is pretty straightforward...
I need to be able to extract row values from table A and update into table B when A.id = B.id.
So for example if i have table A
A
id Name Address Number Employer account Zip
1 John CA 555 ABC
2 Joe GA 777 NBC
3 Alan CO 888 CBS
4 Drew AZ 999 FOX
and i have the table B
B
id fieldid fieldvalue
1 5 011
1 6 90007
2 5 022
2 6 852225
3 5 0333
3 6 87654
4 5 0653
4 6 87656
So basically the account and zip are stored in table B with fieldid values 5and 6 , so i need to update table A with these values. This is a simplified example, i will actually be updating 34 fields.
I have tried using a join in my update statement but it does not return anything close to the estimated amount(returns around 400 when i want 400K)
I have used the full outer join and left join but both give me the same values.
What would be the best way to update the table.
May 22, 2008 at 4:04 pm
try something like:
SELECT a.id, a.Name, a.Address, A.Number, A.Employer, B.Zip
FROM 1sttable 1tablealias
INNER JOIN 2ndtable 2ndtablealias
ON a.id = b.id
so if the tables were A = People, B = ZipTable
SELECT a.id, a.Name, a.Address, a.Number, a.Employer, b.Zip
FROM People a
INNER JOIN ZipTable b
ON a.id = b.id
The long form without using an alias would be:
SELECT People.id, People.Name, People.Address, People.Number, People.Employer, ZipTable.Zip
FROM People INNER JOIN ZipTable
ON People.id = ZipTable.id
You said you want to UPDATE the values?
What I just gave you is a SELECT query designed to present the data normalized into 2 tables back into the data's correct, complete record form.
An UPDATE is another matter entirely.
Do you want to CHANGE the values stored in a database row for a particular column or not?
May 22, 2008 at 4:08 pm
Chad,
Do you mean i should select into a temporary table and then update table A with rows from the temp table?
I would like to directly update . Let me know..
May 22, 2008 at 4:33 pm
it sounds like you want to do:
INSERT INTO TableC (or #tmptbl whatever)
SELECT * FROM A WHERE A.ID IN (SELECT ID FROM B)
UPDATE TableC SET [TargetField] = ?? WHERE ???
Then update table A with the new values in table C....?
Is that what you want to do?
I'm still a little unclear on what it is you're exactly trying to do.
Your original examples are somewhat garbled. Example, it looks like you have State info in an Address field?
What?
Maybe give a clearer example of your data relationship.
Sorry.
-Chad
May 22, 2008 at 4:40 pm
SET NOCOUNT ON
DECLARE @TableA TABLE (id int, Name varchar(10), Address varchar(20), Number int, Employer varchar(10), account int, Zip varchar(10))
INSERT INTO @TableA (id, Name, Address, Number, Employer)
SELECT 1, 'John', 'CA', 555, 'ABC' UNION ALL
SELECT 2, 'Joe', 'GA', 777, 'NBC' UNION ALL
SELECT 3, 'Alan', 'CO', 888, 'CBS' UNION ALL
SELECT 4, 'Drew', 'AZ', 999, 'FOX'
DECLARE @TableB TABLE (id int, fieldid int, fieldvalue varchar(10))
INSERT INTO @TableB
SELECT 1, 5, '011' UNION ALL
SELECT 1, 6, '90007' UNION ALL
SELECT 2, 5, '022' UNION ALL
SELECT 2, 6, '852225' UNION ALL
SELECT 3, 5, '0333' UNION ALL
SELECT 3, 6, '87654' UNION ALL
SELECT 4, 5, '0653' UNION ALL
SELECT 4, 6, '87656'
SELECT * FROM @TableA
UPDATE A
SET Account = FieldValue
FROM @TableA A
INNER JOIN @TableB B
ON A.ID = B.ID
WHERE B.FieldID = 5
UPDATE A
SET Zip = FieldValue
FROM @TableA A
INNER JOIN @TableB B
ON A.ID = B.ID
WHERE B.FieldID = 6
SELECT * FROM @TableA
May 22, 2008 at 5:34 pm
Ok, firstly the example was for demonstration purposes only, i wouldnt insert data into a table like that.
Take the following schema
CREATE TABLE [dbo].[CustomerData](
[customerDataID] [int] IDENTITY(1,1) NOT NULL,
[customerID] [int] NOT NULL,
[fieldID] [int] NOT NULL,
[fieldValue] [varchar](255) NULL,
[validated] [bit] NOT NULL
----The following was table A i was referring to----
CREATE TABLE [dbo].[Customers_Dimension](
[CustomerKey] [int] IDENTITY(1,1) NOT NULL,
[customerID] [int] NULL,
[firstname] [varchar](255) NULL,
[lastname] [varchar](255) NULL,
[varchar](255) NULL,
[username] [varchar](50) NULL,
[password] [varchar](50) NULL,
[optIn] [bit] NULL,
[CanContact] [varchar](50) NULL,
[placeToContact] [varchar](50) NULL,
[timeToContact] [varchar](50) NULL,
[unsubscribed] [char](10) NULL,
[bounced] [char](10) NULL,
[tcAccepted] [bit] NULL,
[tcAcceptedDate] [datetime] NULL,
[tcAcceptedIP] [varchar](50) NULL,
[lastLogin] [datetime] NULL,
[passwordsetup] [nchar](10) NULL,
[datranSent] [bit] NULL,
[advaliantSent] [bit] NULL,
[coverClicksent] [bit] NULL,
[customerReferenceID] [int] NULL,
[ref1firstname] [varchar](50) NULL,
[ref1LastName] [varchar](50) NULL,
[ref1Phone] [varchar](50) NULL,
[ref1Relationship] [varchar](50) NULL,
[ref1phonenumber] [varchar](50) NULL,
[ref2phonenumber] [varchar](50) NULL,
[ref2relationship] [varchar](50) NULL,
[customerPhoneID] [int] NULL,
[HomeNumber] [varchar](max) NULL,
[CellNumber] [varchar](max) NULL,
[worknumber] [varchar](max) NULL,
[customerPersonalID] [int] NULL,
[mothersMaiden] [varchar](50) NULL,
[birthYear] [smallint] NULL,
[birthMonth] [tinyint] NULL,
[birthDay] [tinyint] NULL,
[Dob] [varchar](max) NULL,
[gender] [varchar](50) NULL,
[issuedIDState] [varchar](50) NULL,
[maritalStatus] [varchar](50) NULL,
[UScitizen] [bit] NULL,
[activeMilitary] [bit] NULL,
[customerHousingID] [int] NULL,
[ownHome] [bit] NULL,
[address] [varchar](255) NULL,
[StreetName] [varchar](max) NULL,
[StreetNumber] [varchar](max) NULL,
[city] [varchar](max) NULL,
[Town] [varchar](max) NULL,
[state] [varchar](max) NULL,
[County] [varchar](max) NULL,
[country] [varchar](max) NULL,
[zip] [varchar](max) NULL,
[postcode] [varchar](50) NULL,
[HouseNumber] [varchar](50) NULL,
[yearsAtResidence] [bit] NULL,
[monthsAtResidence] [tinyint] NULL
]
I wanto run an update to update these fields in the customersdimension table, wherever there is a join on the customerid.
This is how i update the aba field-------
update cu
set aba = aba.fieldvalue
from leaplabblade05.leadtran.dbo.customers c
full outer join leaplabblade05.datawarehouse.dbo.customers_dimension cu on c.customerid = cu.customerid
full outer join leaplabblade05.leadtran.dbo.customerdata aba on aba.customerid = cu .customerid
where aba.fieldid ='22' and cu.channel = '5'
Now as you can see i do this for each of the fields in customersdimension that need updates so you would get something like----
update cu
set aba = aba.fieldvalue ,
accountnumber = accountnumber.fieldvalue,
bankname= bankname.fieldvalue ,
directdepositCurrently = directdeposit.fieldvalue,
dob =dob.fieldvalue,
empMonths = empMonths.fieldvalue,
empName = empName.fieldvalue,
empyears = empyears.fieldvalue ,
income = income.fieldvalue ,
incometype = incometype.fieldvalue ,
paydate1 = paydate1.fieldvalue ,
paydate2 = paydate2.fieldvalue,
payperiod = payperiod.fieldvalue,
rentown = rentown.fieldvalue ,
resmonths = resmonths.fieldvalue,
resyears = resyears.fieldvalue ,
activemilitary = activemilitary.fieldvalue,
bankphone = bankphone.fieldvalue ,
dlnumber = dlnumber.fieldvalue ,
dlstate = dlstate.fieldvalue ,
EmployerAddress = Employeraddress.fieldvalue ,
Employercity = employercity.fieldvalue ,
EmployerState = employerstate.fieldvalue,
Employerzip = employerzip.fieldvalue ,
gender = gender.fieldvalue ,
loanamount = loanamount.fieldvalue ,
ref1firstname = ref1firstname.fieldvalue ,
ref1lastname = ref1lastname.fieldvalue ,
ref1phonenumber = ref1phonenumber.fieldvalue ,
ref1relationship = ref1relationship.fieldvalue ,
ref2relationship = ref2relationship.fieldvalue ,
workext = workext.fieldvalue
from leaplabblade05.leadtran.dbo.customerdata c
left join leaplabblade05.datawarehouse.dbo.customers_dimension cu on c.customerid = cu.customerid
left join leaplabblade05.leadtran.dbo.customerdata aba on cu.customerid = aba .customerid
left join leaplabblade05.leadtran.dbo.customerdata accountnumber on aba.customerid = accountnumber.customerid
left join leaplabblade05.leadtran.dbo.customerdata bankname on accountnumber.customerid = bankname.customerid
left join leaplabblade05.leadtran.dbo.customerdata directdeposit on bankname.customerid = directdeposit.customerid
left join leaplabblade05.leadtran.dbo.customerdata dob on directdeposit.customerid = dob.customerid
left join leaplabblade05.leadtran.dbo.customerdata empMonths on dob.customerid = empMonths.customerid
left join leaplabblade05.leadtran.dbo.customerdata empName on empMonths.customerid = empName.customerid
left join leaplabblade05.leadtran.dbo.customerdata empyears on empName.customerid = empyears.customerid
left join leaplabblade05.leadtran.dbo.customerdata income on empyears.customerid = income.customerid
left join leaplabblade05.leadtran.dbo.customerdata incometype on income.customerid = incometype.customerid
left join leaplabblade05.leadtran.dbo.customerdata paydate1 on incometype.customerid = paydate1.customerid
left join leaplabblade05.leadtran.dbo.customerdata paydate2 on paydate1.customerid = paydate2.customerid
left join leaplabblade05.leadtran.dbo.customerdata payperiod on paydate2.customerid = payperiod.customerid
left join leaplabblade05.leadtran.dbo.customerdata rentown on payperiod.customerid = rentown.customerid
left join leaplabblade05.leadtran.dbo.customerdata resmonths on rentown.customerid = resmonths.customerid
left join leaplabblade05.leadtran.dbo.customerdata resyears on resmonths.customerid = resyears.customerid
left join leaplabblade05.leadtran.dbo.customerdata activemilitary on resyears.customerid = activemilitary.customerid
left join leaplabblade05.leadtran.dbo.customerdata bankphone on activemilitary.customerid = bankphone.customerid
left join leaplabblade05.leadtran.dbo.customerdata dlnumber on bankphone.customerid = dlnumber.customerid
left join leaplabblade05.leadtran.dbo.customerdata dlstate on dlnumber.customerid = dlstate.customerid
left join leaplabblade05.leadtran.dbo.customerdata EmployerAddress on dlstate.customerid = EmployerAddress.customerid
left join leaplabblade05.leadtran.dbo.customerdata Employercity on EmployerAddress.customerid = Employercity.customerid
left join leaplabblade05.leadtran.dbo.customerdata EmployerState on Employercity.customerid = EmployerState.customerid
left join leaplabblade05.leadtran.dbo.customerdata Employerzip on EmployerState.customerid = Employerzip.customerid
left join leaplabblade05.leadtran.dbo.customerdata gender on Employerzip.customerid = gender.customerid
left join leaplabblade05.leadtran.dbo.customerdata loanamount on gender.customerid = loanamount.customerid
left join leaplabblade05.leadtran.dbo.customerdata ref1firstname on loanamount.customerid = ref1firstname.customerid
left join leaplabblade05.leadtran.dbo.customerdata ref1lastname on ref1firstname.customerid = ref1lastname.customerid
left join leaplabblade05.leadtran.dbo.customerdata ref1phonenumber on ref1lastname.customerid = ref1phonenumber.customerid
left join leaplabblade05.leadtran.dbo.customerdata ref1relationship on ref1phonenumber.customerid = ref1relationship.customerid
left join leaplabblade05.leadtran.dbo.customerdata ref2relationship on ref1relationship.customerid = ref2relationship.customerid
left join leaplabblade05.leadtran.dbo.customerdata workext on ref2relationship.customerid = workext.customerid
where aba.fieldid = '22' and accountnumber.fieldid = '23' and bankname.fieldid = '21' and directdeposit.fieldid = '12' and dob.fieldid = '44' and empmonths.fieldid = '34' and empname.fieldid = '26' and empyears.fieldid = '33' and income.fieldid = '27' and incometype.fieldid ='25' and paydate1.fieldid ='45' and paydate2.fieldid ='46' and payperiod.fieldid ='28' and rentown.fieldid = '14'and resmonths.fieldid = '9' and resyears.fieldid = '8' and activemilitary.fieldid ='99' and bankphone.fieldid = '24'and dlnumber.fieldid ='19' and dlstate.fieldid ='20' and employeraddress.fieldid ='29' and ref1relationship.fieldid = '39' and employercity.fieldvalue = '30' and employerzip.fieldid ='32' and gender.fieldid ='50' and loanamount.fieldid ='63' and ref1firstname.fieldid = '36' and ref1lastname.fieldid = '37' and ref1phonenumber.fieldid = '38' and workext.fieldid ='60'
and cu.channel = '5'
Howevr, as i mentioned if i do it like that it does not return the target number of rows
Hope its clearer now, it should be, i thought of simplifying it, but i guess that didnt work.
Thanks..
May 23, 2008 at 6:14 am
You can certainly update from an inner join. That's easy. The trick is to get the join right to start with.
Instead of doing an update, write the whole thing as a SELECT to start with. Once you're reliably returning the values you think you should, then you just change that into the UPDATE query.
Nothing you've written jumps out as a problem except that I don't see any INNER JOINS.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 23, 2008 at 9:05 am
Grant has great advice for you here. That is exactly what I do anytime I am writing anything but a simple UPDATE. Once you are sure that your JOINs and filters are producing the correct result set, you can change the statement over to an update and you know exactly which rows will be updated.
I do this for deletes and inserts as well.
May 27, 2008 at 8:20 pm
my mistake, I see what you're doing now.
the select would tell you that you have the criteria correct, and hopefully will give you a better idea of what's missing that should be there.
it might be your join type but i doubt it. The lefts look ok...
I would start with making sure my criteria is right, use a select to from join 1 progressivly making sure I'm getting the results I want...if 1 of your criteria is incorrect...
If I'm not getting expected results, I usually start with breaking down the statement to known results, then I build it up until it fails and inspect that point in the chain. Pretty basic really but amazingly effective, usually.
But I don't see anything wrong with the statement itself.
Do you normally interact that entire group of tables? Perhaps some of them allow selects but require higher priviledges than the account you're using for update/delete/insert?
-Chad
May 27, 2008 at 9:14 pm
Think about it... the skinny table is an "EAV" (Entity, Attribute, Value)... a cross tab Select is all that is needed as the source of the update. Would be nasty fast, too.
Post some data for the skinny table using the principles found in the URL in my signature line so I can show you how...
--Jeff Moden
Change is inevitable... Change for the better is not.
May 28, 2008 at 7:11 am
The LEFT JOINs are IMHO incorrect. The way you have written the query, only those rows where ALL columns should be updated will be affected. By putting all these additional conditions into the WHERE clause, you have effectively canceled any LEFTness of the joins.
All conditions should be inside the join, like that:
--this means that any rows with aba.fieldid other than '22' (incl. NULL) will be skipped =>actually it is INNER JOIN
LEFT JOIN leaplabblade05.leadtran.dbo.customerdata aba ON cu.customerid = aba.customerid
WHERE aba.fieldid = '22'
--correct left join
LEFT JOIN leaplabblade05.leadtran.dbo.customerdata aba ON cu.customerid = aba.customerid AND aba.fieldid = '22'
Anyway, I'm sure Jeff will give you better performing solution if you supply him with some data, so this is just to explain what went wrong with your update.
May 28, 2008 at 12:28 pm
wow, I can't believe I missed that about the lefts. Sorry about that. I'll just stick more with lurking before I post... 🙂
Jeff, thanks for those guidelines.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply