Updating one table from another

[m00nbeast]

Hi you wonderful helpers who generously give your time away to help the less competent :)

I thought this would be an easy task but I'm not an advanced SQL guy and don't know how to use an inner join, which apparently I need to use.

I have two tables

tblEmp
fname (text)
lname (text)
balance (money)

beginning_balances
fname (text)
lname (text)
balance (money)

I want to update the balances from beginning_balances to tblEmp.

Here's my attempt to recreate the example I was tyring to follow which yeilded zero results although I know that there are exact fname and lname matches:

Update tblemp
Set    tblemp.balance = beginning_balances.balance
From   tblemp
       Inner Join beginning_balances
         On tblemp.fname = beginning_balances.fname
         And tblemp.lname = beginning_balances.lname
Where  tblemp.balance = 0
 

Am I completely off here?

[vijaii]

Hi you wonderful helpers who generously give your time away to help the less competent :)

I thought this would be an easy task but I'm not an advanced SQL guy and don't know how to use an inner join, which apparently I need to use.

I have two tables

tblEmp
fname (text)
lname (text)
balance (money)

beginning_balances
fname (text)
lname (text)
balance (money)

I want to update the balances from beginning_balances to tblEmp.

Here's my attempt to recreate the example I was tyring to follow which yeilded zero results although I know that there are exact fname and lname matches:

Update tblemp
Set    tblemp.balance = beginning_balances.balance
From   tblemp
       Inner Join beginning_balances
         On tblemp.fname = beginning_balances.fname
         And tblemp.lname = beginning_balances.lname
Where  tblemp.balance = 0
 

Am I completely off here?

The query is correct but for what purpose you are using

Where tblemp.balance = 0

This means it will update only records which as 'balance' as '0' in 'tblemp' table.

[Vidhura]

Hi you wonderful helpers who generously give your time away to help the less competent :)

I thought this would be an easy task but I'm not an advanced SQL guy and don't know how to use an inner join, which apparently I need to use.

I have two tables

tblEmp
fname (text)
lname (text)
balance (money)

beginning_balances
fname (text)
lname (text)
balance (money)

I want to update the balances from beginning_balances to tblEmp.

Here's my attempt to recreate the example I was tyring to follow which yeilded zero results although I know that there are exact fname and lname matches:

Update tblemp
Set    tblemp.balance = beginning_balances.balance
From   tblemp
       Inner Join beginning_balances
         On tblemp.fname = beginning_balances.fname
         And tblemp.lname = beginning_balances.lname
Where  tblemp.balance = 0
 

Am I completely off here?

The data types text are incompatible in the equal to operator.(
tblemp.fname = beginning_balances.fname)

The problem is a datatype of fname and lname.

Can't use varchar instead of text.? Refer the following link

Text datatype

[hariharanmca]

Hi you wonderful helpers who generously give your time away to help the less competent :)

I thought this would be an easy task but I'm not an advanced SQL guy and don't know how to use an inner join, which apparently I need to use.

I have two tables

tblEmp
fname (text)
lname (text)
balance (money)

beginning_balances
fname (text)
lname (text)
balance (money)

I want to update the balances from beginning_balances to tblEmp.

Here's my attempt to recreate the example I was tyring to follow which yeilded zero results although I know that there are exact fname and lname matches:

Update tblemp
Set    tblemp.balance = beginning_balances.balance
From   tblemp
       Inner Join beginning_balances
         On tblemp.fname = beginning_balances.fname
         And tblemp.lname = beginning_balances.lname
Where  tblemp.balance = 0
 

Am I completely off here?

You can learn Select Statement and JOIN here

[m00nbeast]

The query is correct but for what purpose you are using

Where tblemp.balance = 0

This means it will update only records which as 'balance' as '0' in 'tblemp' table.

Actually every balance in tblemp is at zero so it is unnecessary. I should have left that out. sorry.

[m00nbeast]

The data types text are incompatible in the equal to operator.(
tblemp.fname = beginning_balances.fname)

The problem is a datatype of fname and lname.

Can't use varchar instead of text.? Refer the following link

Text datatype

I apologize. I double checked and they are all nvarchar. I tried to change the OP to reflect this but the edit button is missing.

Heres what I found out. I tried to even just get records to come back:

SELECT t.fname, t.lname, t.balance, b.fname, b.lname, b.balance
FROM tblemp t
INNER JOIN beginning_balances b
ON t.fname = b.fname AND t.lname = b.lname

but it returned nothing so I dug deeper to find if I do a simple select statement on a name I know is in both tables like:

Select fname, lname from beginning_balances where lname = 'proctor' 

it brings back 1 record with:

JAMIE PROCTOR

but if I try

Select fname, lname from beginning_balances where fname= 'jamie' 

it doesn't come back with any records like it does on tblemp. I imported this list of names from an excel file if that helps any. Is there an easy way to check if there are hidden leading or trailing spaces and get rid of them perhaps or just account for them?

[m00nbeast]

UGH! I just confirmed that all the fnames in beginning_balances have leading spaces in them! What's the quickest way to get rid of them? Change it in Excel somehow and then reimport? Create a new table and write to it while stripping out the leading spaces.... erm... somehow.

[m00nbeast]

Yay, I accomplished my goal with help from someone:

UPDATE beginning_balances SET fname = rtrim(ltrim(fname))

and then got the balance update finished with this:

UPDATE tblemp
SET tblEmp.balance = b.balance
FROM tblemp INNER JOIN 
    (SELECT beginning_balances.fname, beginning_balances.lname, beginning_balances.balance FROM beginning_balances) AS b
ON tblEmp.fname = b.fname AND tblEmp.lname = b.lname