SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 SQL Alter Table Statement		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

rustynails28
Starting Member

United Kingdom
12 Posts
Posted - 06/28/2012 :  06:35:13  Show Profile  Reply with Quote
Excuse me if im wrong but I am trying to do the following:

I have a table (tblAsset) and I have an column (UID)(which is empty currently). I have a old table with the (UID) values that I would like to insert into this column. BUT the values in my old table have to match the serial number from the old table to the serial number in the new table (tblAsset).

Now there is a scenario that there may be a discrepancy with the values in my new table, so I will require a query that returns with the (UID) numbers that were imported successfully and (UID) numbers that were unsuccessful.

Im assuming the code will start like:

ALTER TABLE tblAsset

MODIFY COLUMN UID nvarchar(20)

WHERE old_table.SerialNumber = tblAsset.SerialNumber


Thats where I get stuck.... All help welcome

sunitabeck
Flowing Fount of Yak Knowledge

5152 Posts
Posted - 06/28/2012 :  06:59:24  Show Profile  Reply with Quote
ALTER TABLE is a DDL statement - i.e., it is used to modify the table object itself. For example, when you want to add or modify a column. UPDATE is the DML statement - i.e., what you would use to update the data in a table.

So you would need two statements: One to add the column to the table object, and second to insert/update the data in that newly added column.

From your description, it looks like you already have the column in the destination table. Assuming you do, to get the data into that column, your query should be like this:
UPDATE t SET
   t.UID = o.UID
-- SELECT o.SerialNumber, o.UID, t.UID
FROM
   tblAsset t
   INNER JOIN old_table o ON
      o.SerialNumber = t.SerialNumber;
This assumes that the SerialNumber is a primary/unique key candidate. If not, for example, if you had two rows in tblAsset with the same SerialNumber and one row in the old_table, both rows will get the same UID. If you had two rows with the same serial number in both tables, the UID that be copied to tblAsset is unpredictable.

If you run the select statement that I have commented out, you will see what is going to be updated. You can change the INNER JOIN to a FULL JOIN to see the rows that are in one table but not in the other along with all the matching rows.
Go to Top of Page

rustynails28
Starting Member

United Kingdom
12 Posts
Posted - 06/28/2012 :  07:41:50  Show Profile  Reply with Quote

Sorry my lack of knowledge with SQL, I assumed I wanted to alter table after reading your comments it is an UPDATE statement that meets my requirements.

The Serial number is unique to each item within my inventory but I am taking into account human and also the possibility there are NULL Values. So basically a table which has:

tblOld (Table) tblAsset (Table)

Fields: SerialNumber UID SerialNumber UID

Values: GK400GK 1500 GK400GK NULL

I need the SQL now to compare tblOld.SerialNumber (tblOld) table and check the tblAsset.SerialNumber (tblAsset) table

THEN INSERT to the tblAsset.UID (tblAsset) table IF a match is found.

THEN RETURN THE VALUES THAT WASNT SUCESSFUL;

Hope that helps, thanks for everyones help
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5152 Posts
Posted - 06/28/2012 :  08:02:48  Show Profile  Reply with Quote
You can use the SELECT statement I had posted earlier:
SELECT 
   *
FROM
   tblAsset t
   FULL JOIN old_table o ON
      o.SerialNumber = t.SerialNumber
You can dd a WHERE clause to the end of that to see which SerialNumbers match and which don't. For example the following will show you the rows in tblAsset that don't have a corresponding SerialNumber in the old_table: 
SELECT 
   *
FROM
   tblAsset t
   FULL JOIN old_table o ON
      o.SerialNumber = t.SerialNumber
WHERE
   o.SerialNumber IS NULL
Regarding duplicate serial numbers, ideally you should add a unique constraint to the tables if in-fact they should be unique. That will take away the possibility of users entering duplicate data. If you want to see if there are duplicates, you can run the following query:
SELECT
	SerialNumber
FROM
	old_table
GROUP BY
	SerialNumber
HAVING
	COUNT(*) > 1
Go to Top of Page

rustynails28
Starting Member

United Kingdom
12 Posts
Posted - 06/28/2012 :  09:07:26  Show Profile  Reply with Quote
I got some of my code working except this part

---- Return Unsuccessful rows
SELECT tblOld.*
FROM tblOld
LEFT JOIN tblAsset
ON tblAsset.SerialNumber = tblOld.SerialNumber
WHERE tblAsset.SerialNumber IS NULL

Im getting the error Msg 4145, Level 15, State 1, Line 4

An expression of non-boolean type specified in a context where a condition is expected, near 'SerialNumber'.????

Please help
Go to Top of Page

jimf
Flowing Fount of Yak Knowledge

USA
2868 Posts
Posted - 06/28/2012 :  09:46:30  Show Profile  Reply with Quote
Your WHERE clause looks good here, make sure you didn't actually do
WHERE tblAsset.SerialNumber ISNULL

Jim


Everyday I learn something that somebody else already knew
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000