SQL Alter Table Statement - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL Alter Table Statement

Author

Topic

rustynails28
Starting Member

12 Posts

Posted - 2012-06-28 : 06:35:13

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
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-06-28 : 06:59:24

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.

rustynails28
Starting Member

12 Posts

Posted - 2012-06-28 : 07:41:50

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

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-06-28 : 08:02:48

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

rustynails28
Starting Member

12 Posts

Posted - 2012-06-28 : 09:07:26

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

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-06-28 : 09:46:30

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources