Modify one table's data with other

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

Modify one table's data with other

Author

Topic

razeena
Yak Posting Veteran

54 Posts

Posted - 2011-07-26 : 05:54:19

Declare @AcademyTable table(strname varchar(20),Price int)
insert into @AcademyTable
select 'Master''s',3000 union all
select 'Bachelors',2000 union all
select 'Precollege',1000

select * from @AcademyTable

Declare @PriceTable table(categoryid int,Price int)
insert into @PriceTable
select '1',500 union all
select '2',200 union all
select '3',100

select * from @PriceTable

The category coreesponding to master =1,bachelor=2 and precollege=3.
There is no table to link them between.
I need to update the Prices of PriceTable with the prices of AcademyTable.
The expected result is

Categoryid,Price
---------------
1,3000
2,2000
3,1000

How shall I join these 2 tables using the categoryid?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-07-26 : 06:56:34

There has to be some way for SQL Server to know how to map. So you may need to create a mapping table. You could even construct one on the fly, like this:


;WITH MappingCTE(categoryId,strName) AS
(
	SELECT 1,CAST('Master''s' AS VARCHAR(32)) UNION ALL
	SELECT 2,CAST('Bachelors' AS VARCHAR(32)) UNION ALL
	SELECT 3,CAST('Precollege' AS VARCHAR(32))
)
	
--SELECT
--	p.categoryId,
--	a.Price
UPDATE p SET
	Price = a.Price
FROM
	@PriceTable p
	INNER JOIN MappingCTE t ON t.categoryId = p.categoryid
	INNER JOIN @AcademyTable a ON a.strname = t.strName;
	
SELECT * FROM @PriceTable;

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-07-26 : 17:40:49

Read ISO-11179 rules or any book on basic RDBMS and data modeling. We do not put that silly “table” affix on a table name, or the data type in the column names. Tables have keys, constraints, etc. A price as a non-decimal value? Weird.

CREATE TABLE Academies
(program_name VARCHAR(20) NOT NULL PRIMARY KEY,
program_price DECIMAL (8, 2) NOT NULL
CHECK (program_price > 0.00)
);

Learn to use the row constructor with INSERT INTO statements.

INSERT INTO Academies
VALUES ('Master', 3000.00),
('Bachelor', 2000.00),
('Precollege', 1000.00);

There is no such thing as a “category_id” in RDBMS. A data element can be a “<something>_category” or a “<something>_id”, never that silly hybrid you used. Also, why did you declare it to be an INTEGER, then put strings in it?

CREATE TABLE PriceList
(price_category INTEGER NOT NULL PRIMARY KEY,
foobar_price DECIMAL (8,2) NOT NULL
CHECK (foobar_price > 0.00)
);

INSERT INTO PriceList
VALUES (1, 500.00), (2, 200.00), (3, 100.00);

>> The category corresponding to master =1, bachelor=2 and precollege=3. <<

NO! Read a book on RDBMS before you get near a database again. look up the “Information Principle” in Dr. Codd's rules. It will be in the front of book. All relationships are shown as scalar values in the columns of the rows of tables. So where is this correspondence shown in your schema? NOWHERE!

>> There is no table to link [sic] them between. <<

Did you know that “link” is a term from network databases and assembly language programming? It does not exist in RDBMS? So this pile of junk is not a complete schema because it violated the Information Principle.

My guess is that you think a table is a 1950's deck of punch cards and you can depend on sequential order for some kind of matching (note I said, matching and not JOIN; joins use a predicate).

You either need a relationship table, or to use the program_name in the new price list table instead of an integer. See if your boss will pay for some basic SQL education. Of course, it might be cheaper and safer to just fire you ...

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources