Join SQL Update

Folks,

Don't know about you but I've wondered on occasion whether or not it's possible to do a join update in Oracle and SQL Server as I've seen done in mySQL. For those who've never seen an update join, here's one to illustrate:

Old school:

update invc_item
set feed_id =
(select max(feed_id)
from calls
where invc_item_id = invc_item.invc_item_id)
where feed_id = 0
and exists
(select *
from calls
where invc_item_id = invc_item.invc_item_id
and feed_id > 0)

As you can see in the example, this SQL re-queries calls twice - once for the update and once for the where.

Update join:

update i
set i.feed_id = c.feed_id
from invc_item i, calls c
where i.feed_id = 0
and c.invc_item_id = i.invc_item_id
and c.feed_id > 0

Much simpler, far more efficient, and once you get the hang of it - less prone to error when doing these sorts of updates.

I'm not sure whether or not you have to use the table aliases as I did. Haven't tested without. This is how it was illustrated in the documentation I found.

Now for the bad news... SQL Server (the toy wannabe database) supports it, Oracle does not.

Thanks for the help Scott...

How to know what table is linking to the row you are trying to delete

You are trying to delete a row in a table but b/c of the db constraint you can't, here how to find out what table is causing the issue.

In oracle you would get an error like this:
ORA-02292: integrity constraint (TESTDB.FK_REF_114571) violated - child record found

select * from user_constraints where constraint_name = 'FK_REF_114571'

In MSSQL
select * from INFORMATION_SCHEMA.constraint_column_usage
where constraint_name = 'FK_REF_114571'

How to copy data between 2 different Oracle or MSSQL database

Well what if you did not do a backup of your data and you deleted the wrong stuff. Import your backup database on the same server. Login with a user that have access to both DB, system should work.
Oracle to Oracle:

insert into productiondb.bill_detail select * from backupdb.bill_detail
where not exists
(select *
from productiondb.bill_detail
where bill_id = backupdb.bill_detail.bill_id)

This query will copy everything that is missing back into the production DB.

MSSQL to MSSQL:
Here's how I populated nd_loc and it took only 10 or so seconds to copy ~150,000 rows! First connect to the production db and then create the link to the backup server. By running the command "sp_addlinkedserver sqldev" (where sqldev is the other server name where the backup db is. You don't have to do this if both db are on the same server)

insert into nd_loc select * from sqldev.backupdb.dbo.nd_loc

Now why can't their damned odbc driver be that fast? (Thanks Scott for the tips)

How to create a table from a select statement

Ready to make a big update, before you do make a backup of that data into a temp table with a select. This save me few times.

create table tmp_eric as 
( select * from ... where ... )

Each columns returned from the select will become the columns of the new table tmp_eric.

For MSSQL, you need to first create the table then you can use the insert

create table tmp_eric (col1, INTEGER PRIMARY KEY, col2...)

insert into tmp_eric (col1, col2,...)
select col1, col2,... from eric

You can also use:

select * into tmp_eric from eric