Syntax Help PLEASE

Question

Syntax Help PLEASE

g_dion

SSC Journeyman

Points: 94
More actions
January 21, 2010 at 4:03 pm

#141834

Hi I am trying to import data from a spreadsheet into a NEW table (Table_new), which will eventually replace data in a certain column in an existing table (table_old). I can import into new table, no problem there. What I need is help creating the syntax for the compare and update.
Table_new only has 2 columns, column Device_name and Device_Serial Number. Table _old has these exact same columns, among others. What I want to do is compare the Device_name of Table_new with Device_name of table_old, and if they match, then insert the data from column Device_Serial Number into the table_old column with the same name.
Could somebody please show me the syntax of the query? Additionally should this be a transaction or a query. I am new to SQL and really do not know the difference.
Thanks in Advance
Grant

Viewing 5 posts - 1 through 4 (of 4 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

you'd get much better,understandable answers if you were able to provide the CREATE TABLE definitions of your two tables; psuedo code like table_new and table_old is not nearly as useful to your understanding as AllDevices and DeviceSerialNumbers for table names.

an update from another table is structured like this:

--the table to change

UPDATE table_old

--change one or more columns

SET table_old.SerialNumber = table_new.SerialNumber

--from where?

FROM table_new

--how is the data related?

WHERE table_old.Device_name = table_new.Device_name

--don't change data that is already in place

AND ISNULL(table_old.SerialNumber,0) <> table_new.SerialNumber

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Jeff Moden SSC Guru Points: 1004439 More actions · Answer 2

I'm not sure that you need the ISNULL, Lowell... if "0" isn't a value in the table, NULL will give the same result as "0" because NULL isn't even equal to another NULL. Having the ISNULL makes the criteria non-sargeable.

--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)

g_dion SSC Journeyman Points: 94 More actions · Answer 3

Lowell

Thanks for your answer. I am brand new to SQL, and do not know why you wanted the Create Table stuff. The database and tables were already there, I just wanted to replace the SNs in the table with a list from a flat XLS sheet that had been manually verified. Some of the SNs matched in both tables for certain device names, others did not. Your answer was great, I was able to do what I wanted to do. I hope I will eventually learn more SQL syntax to be able to generate good results quickly.

Lowell SSC Guru Points: 323518 More actions · Answer 4

g_dion (1/25/2010)
... Create Table stuff. The database and tables were already there,....

glad i could help a bit g_dion; i'm glad you were able to do what you needed to.

what i meant about the CREATE table thing is perspective....in your case, at your laptop for example, you can see the database, and the tables were there..for you.

for me, on the other side of the internet, it's not as easy for me to see the whole "everything is already there".. i have to create and infer a lot...so the more info you can give, the better and more accurate the information anyone here can give you.

This is especially true if you are new to SQL; i doubt very much you really have tables named table_old and table_new, right?

if you gave us real table and column names, it might help you understand the concept a lot better, since you'd be familiar with the tables and the data. then you can concentrate on any conepts in the SQL we provide.

when you can give us tables and data where we can copy/paste, then add to it and test, it makes it so easy for us here on SSC we want to help...we can spend time finding a nice solution for you.

good luck and keep at it!

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!