Database

ptr_Electron wrote:

what is the command on the MYSQL promt to invoke editor to write Stored procedure in MYSQL

Mycroft Holmes wrote:

I was hoping for a quick confirmation

Mycroft Holmes wrote:

each has a clustered index scan cost over 80%

Mycroft Holmes wrote:

The query retuens 3.4m rows from a base table of 16m with lots of linkages

select text from syscomments where id = object_id('procname')

Nostrom0 wrote:

I want to insert the data from the three columns in the old table, Flag1, Flag2, and Flag3, into their corresponding columns in the new table for each matching part number. The new table has matching columns of the same data type, the cells are just empty

INSERT INTO TABLE_NEW (Flag1, Flag2, Flag3...)
SELECT Flag1, Flag2, Flag3...
FROM TABLE_OLD

UPDATE TABLE_NEW
SET (Flag1, Flag2, Flag3...) = (SELECT Flag1, Flag2, Flag3...
                                FROM TABLE_OLD
                                WHERE TABLE_OLD.PART_NUM=TABLE_NEW.PART_NO)

UPDATE  WEB_PRODUCT_TEST_COPY  p
SET     (
		CONTAINS_MARBLE_FLG, 
		IS_MARBLE_FLG, 
		CONTAINS_SMALL_BALL_FLG, 
		IS_SMALL_BALL_FLG, 
		SMALL_PARTS_FLG, 
		BALLOON_FLG
		) =
(SELECT  
		 CONTAINS_MARBLE_FLG, 
		 IS_MARBLE_FLG, 
		 CONTAINS_SMALL_BALL_FLG, 
		 IS_SMALL_BALL_FLG, 
		 SMALL_PARTS_FLG, 
		 BALLOON_FLG
 FROM    TEST_CPSC_BOOL
 WHERE   PART_NUM = p.PART_NO
);

Nostrom0 wrote:

thank you for the answer

Nostrom0 wrote:

I got an "ORA-01427: single-row subquery returns more than one row".

Nostrom0 wrote:

If it makes any difference, I am using TOAD on an old 8i database

Nostrom0 wrote:

I rolled back the changes I made to the MV table and the database worked again. I believe I need to just insert the info methodically step by step

Nostrom0 wrote:

Aha, you are right - there are some duplicate part numbers in TEST_CPSC_BOOL

UPDATE  WEB_PRODUCT_TEST_COPY  p
SET     (	
	CONTAINS_MARBLE_FLG, 	
	IS_MARBLE_FLG, 	
	CONTAINS_SMALL_BALL_FLG, 	
	IS_SMALL_BALL_FLG, 	
	SMALL_PARTS_FLG, 	
	BALLOON_FLG	
	)
 =(SELECT  	
	 CONTAINS_MARBLE_FLG, 	
	 IS_MARBLE_FLG, 	
	 CONTAINS_SMALL_BALL_FLG, 	
	 IS_SMALL_BALL_FLG, 	
	 SMALL_PARTS_FLG, 	
	 BALLOON_FLG
 FROM    TEST_CPSC_BOOL a1
 WHERE   PART_NUM = p.PART_NO
 AND     NOT EXISTS (SELECT 1
                     FROM TEST_CPSC_BOOL a2
                     WHERE a1.ROWID    < a2.ROWID
                     AND   a1.PART_NUM = a2.PART_NUM));

Nostrom0 wrote:

Wicked! That worked perfectly

Nostrom0 wrote:

If it goes wrong I'll be back tomorrow asking how to turn NULLs into 0's

Nostrom0 wrote:

Thanks again, Mika!

update table_new t1
  set t1.flag1 = ( select t2.flag1 from table_old t2 where t2.part_num = t1.part_num )
     ,t1.flag2 = ( select t2.flag2 from table_old t2 where t2.part_num = t1.part_num )
     ,t1.flag3 = ( select t2.flag3 from table_old t2 where t2.part_num = t1.part_num )
;

 WITH GetTopRecord AS (
    SELECT     TOP (1) RecordID
FROM         [16500Record]
WHERE     (Completed = 'false') AND (CheckedOut = 'false')
 )

 SELECT * FROM [16500Record]
 WHERE [16500Record].RecordID = GetTopRecord.RecordID

Sunset Towers wrote:

WITH GetTopRecord AS (
SELECT TOP (1) RecordID
FROM [16500Record]
WHERE (Completed = 'false') AND (CheckedOut = 'false')
)
SELECT * FROM [16500Record]
WHERE [16500Record].RecordID = GetTopRecord.RecordID

SELECT * 
FROM [16500Record]
WHERE [16500Record].RecordID = (
                   SELECT TOP (1) RecordID
                   FROM   [16500Record]
                   WHERE  (Completed = 'false')
                   AND (CheckedOut = 'false')
                   ORDER BY ???)

WITH GetTopRecord (id) AS (
        SELECT   TOP (1) RecordID
       	FROM     [16500Record]        
 	WHERE    (Completed = 'false') 
        AND      (CheckedOut = 'false')
        ORDER BY RecordID
)	     
SELECT * FROM [16500Record], GetTopRecord 
WHERE [16500Record].RecordID = GetTopRecord.id

Declare @RecordIDToUpdate int
Set @RecordIDToUpdate = 
 (
      SELECT     TOP (1) RecordID
      FROM         [{0}]
      WHERE     (Completed = 'false') AND (CheckedOut = 'false')
 )

 UPDATE [{0}] 
      SET CheckedOut = 'true', CheckedOutBy = '{1}', CheckedOutDateTime = {2}
      WHERE (RecordID = @RecordIDToUpdate)