Need help with a simple query

Question

Post reply

Need help with a simple query

David Greene-305730

Old Hand

Points: 390
More actions
November 3, 2006 at 10:44 am

#192994

Hello Everyone,
I have two tables in the same Microsoft SQL database. I want to fill the Division field in Table1 with what is in the Division field in Table2. There is a common field in each table named [Salon #].
I apologize in advance for this being such a simple question, but I am in the newbie area.
Any and all help will be greatly appreciated.
Dave

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Try this in a test database first:

update dbo.table1 set

Division = t2.Division

from

dbo.table1 t1

inner join dbo.table2 t2

on (t1.[Salon #] = t2.[Salon #])

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 2

Don't be sorry... we were all there at some point in our career.

David Greene-305730 Old Hand Points: 390 More actions · Answer 3

This is the query I ended up with:

update MasterRegis

set subcompanyname = eui_director_list.concept, street = eui_director_list.f4, streetline2 = eui_director_list.f5, locationdesc = eui_director_list.city, state = eui_director_list.state, zipcode = eui_director_list.zip, division = eui_director_list.division, psstatusdesc = eui_director_list.[director #], correction = eui_director_list.[director name], psactiondescription = eui_director_list.[reg mgr #], loc2 = eui_director_list.[reg mgr], loc3 = eui_director_list.supervisor, loc4 = [supv #], companycode = eui_director_list.company

from masterregis inner join eui_director_list on (masterregis.locationcode = eui_director_list.[salon #])

But when I tried to run it, I got this message:

Error converting data type nvarchar to float.

Any ideas on why I would receive this error?

Thanks in advance for your help.

dave

Farrell Keough SSCoach Points: 17414 More actions · Answer 4

No way... You will have to comment out each line and see where your data choices do not work... We do not know the datatypes from these tables and this would be tedious to check when you have that information right at your finger tips.

Try this:

BEGIN TRANSACTION

UPDATE MasterRegis SET

-- subcompanyname = eui_director_list.concept,

street = eui_director_list.f4,

streetline2 = eui_director_list.f5,

locationdesc = eui_director_list.city,

state = eui_director_list.state,

zipcode = eui_director_list.zip,

division = eui_director_list.division,

psstatusdesc = eui_director_list.[director #],

correction = eui_director_list.[director name],

psactiondescription = eui_director_list.[reg mgr #],

loc2 = eui_director_list.[reg mgr],

loc3 = eui_director_list.supervisor,

loc4 = [supv #], companycode = eui_director_list.company

FROM masterregis

INNER JOIN eui_director_list ON( masterregis.locationcode = eui_director_list.[salon #])

ROLLBACK TRANSACTION

-- COMMIT TRANSACTION

If it works with a line commented out, you will know that those two datatypes are not consistent, (i.e., SQL Server cannot make a implied conversion if they are similar datatypes) and fix your choice or change the datatype.

I wasn't born stupid - I had to study.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

May I suggest a faster technic.

Comment half the columns. Depending wether it works or not, you know in which half the error is so that's 50% of the work done. In the correct half. Comment half the lines again. Repeat untill you find the offending row(s). Keep in mind that you may have more than one problem to find even if you have only 1 error.

Farrell Keough SSCoach Points: 17414 More actions · Answer 6

Excellent addition!

I wasn't born stupid - I had to study.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 7

Ninja's_RGR'us

SSC Guru

Points: 294069

November 16, 2006 at 12:44 pm

#672422

Yup binary search can be manually applied too .

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 8

David,

Tomakeiteasiertotroubleshootyourcodeyoushouldadoptandfollowaformattingstandardthatincludesthepropercasingpunctuationandindention

Intheprocessofformattingyourwillfindthatmanyproblemswiththecodewillsimplyvanishbecauseyouwillbelookingatthecodemorethanonceaswellasit

beingeasiertounderstandforevenyou.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

David Greene-305730 Old Hand Points: 390 More actions · Answer 9

OK, this is what I just tried to run,

UPDATE MasterRegis SET

subcompanyname = eui_director_list.concept,

street = eui_director_list.f4,

streetline2 = eui_director_list.f5,

locationdesc = eui_director_list.city,

state = eui_director_list.state,

zipcode = eui_director_list.zip,

division = eui_director_list.division,

psstatusdesc = eui_director_list.[director #],

correction = eui_director_list.[director name],

psactiondescription = eui_director_list.[reg mgr #],

loc2 = eui_director_list.[reg mgr],

loc3 = eui_director_list.supervisor,

loc4 = [supv #], companycode = eui_director_list.company,

FROM MasterRegis

INNER JOIN eui_director_list ON(masterregis.locationcode = eui_director_list.[salon #])

And got the following error:

Server: Msg 156, Level 15, State 1, Line 15

Incorrect syntax near the keyword 'FROM'.

Gee whiz I must be messin up royally here...

Thanks for all your help!

David

Farrell Keough SSCoach Points: 17414 More actions · Answer 10

Remove the last comma before the FROM statment

change:

loc4 = [supv #], companycode = eui_director_list.company,

to:

loc4 = [supv #], companycode = eui_director_list.company

No biggie. You will get very used to this error the more you code - easy one to miss when typing fast...

I wasn't born stupid - I had to study.

David Greene-305730 Old Hand Points: 390 More actions · Answer 11

OK, thanks for that piece of advice.

But now I am getting this error message:

Server: Msg 8114, Level 16, State 5, Line 1

Error converting data type nvarchar to float.

I thought line one is fine... but my server doesn't like it.

Dave

Farrell Keough SSCoach Points: 17414 More actions · Answer 12

The location given by the error is not always the exact location where the probelm lies. That is why Ninja's RGR'us suggested commenting one half of the code. In my example, you have only one line commented out. Try using that code and comment out the top half of the columns to update. Use the ROLLBACK and you will not affect your table, (SQL Server will run the UPDATE to see if it works, then ROLLBACK the data to its original state).

That way you can determine if your error is in the top or bottom of the UPDATE and then start putting lines back into play and see just precisely which line is erroring.

Once you know that, look at the datatypes for the column to be updated and the column you are updating it with and see if they do not match.

Let us know what you find.

I wasn't born stupid - I had to study.

jezemine SSCrazy Eights Points: 8059 More actions · Answer 13

I think you'd be a lot better off if you adopted a different naming convention for your columns too. [supv #] is pretty bad. how about supervisorNumber or supvNum or supvNo?

putting spaces and non-alphanumeric chars in object names, while possible, is evil.

---------------------------------------
elsasoft.org