I have two table in SQL2005. We will call them table-1 and table-2. Even though they have a different number of columns they do have 5 columns with the same names. What I would like to do is copy these 5 columns from table-1 to table-2 with the following exception and adaptation. In table-2 there is two columns which are bits, we will call them bit-a and bit-b. Here is the exception, while the copy is taking place, before the data is updated from table-1 to table-2, it will check the status of bit-a in table-2. If bit-a is a zero it will copy the row from table-1 to table-2. However it the bit-a is a one it will not copy the data from table-1 to table-2, it will however set bit-b in table-2. Just so you know there are 99 rows and 5 columns. There is also a key column. What I figure has to happen is some sort of incremental counter. If this count is 1 and bit-a in table-2 is a 0 copy row 1 from table-1 to table-2. Now increment the counter. If the counter is 2 and the bit-a is a 0 copy row 2. again increment the counter. If the counter is 3 and the bit-a is a 1 dont copy the data but set bit-b. Increment counter. If counter is 4 and bit-a is a 0 copy row 4 and so on. But it has to be very fast. The last time I wrote code to do something like this it took almost 1.5 min. Way to long. The SQL programmer we had on site who left the company helped me with that and got it to run under 3 seconds. If you need further information please let me know. Because I am still very new to SQL I can not say I am extremely clear. I know what I would like to do but dont always know the correct terms and such. Thanks again and have a great and prosperous new year.
Mark,
I found that post to be confusing, particularly the part about bit-a checking and the counter.
Could you provide your code and the code that your SQL programmer helped you with in the other example?
Are you trying to INSERT or UPDATE data in table 2?
You may need to use a prepared statement to accomplish this.
Incremental Counter may be referring to an auto incrementing primary key.
You can combine SELECT and INSERT queries like this:
INSERT table2 (col1, col2, col3)
SELECT col1, col2, col3
FROM table1
WHERE bita = 1
Hi mrtweaver,
I’m no SQL expert either but I’ll have a go. Bear in mind I only have access to MySQL so this solution may not work in SQL2005.
I’m assuming table1 and table2 both have a fixed size of 99 rows - you mention checking the status of bita in table2, so the rows must already exist. To copy the data use the following SQL:
UPDATE table2 SET
col1=(select col1 from table1 where indexnum=table2.indexnum),
col2=(select col2 from table1 where indexnum=table2.indexnum),
col3=(select col3 from table1 where indexnum=table2.indexnum),
col4=(select col4 from table1 where indexnum=table2.indexnum),
col5=(select col5 from table1 where indexnum=table2.indexnum)
where bita=0
Setting bitb to one if bita is one can be achieved with a separate statement:
UPDATE table2 SET bitb=bitaI can’t immediately see the benefit of this, as the bitb column will always end up the same as the bita column 
Let us know is this helps you make progress. If anyone has a neater solution I’d love to see it.
Al
I like you Al am no expert in SQL, This is why I ask questions to get a better understanding while I attend night school on the stuff. A friend sent me the following snippit and said it would work as well. I am going to try both and see which works better and faster. But here is the snippit maybe you understand the SQL better than I but I will let you know how it works.
comm_error = fpmi.db.runScalarQuery(“SELECT 1 FROM some_db WHERE something = something”)
emp_log = fpmi.db.runScalarQuery(“SELECT 1 FROM some_db WHERE emp = something”)
if !(comm_error or emp_log):
fpmi.db.runUpdateQuery(“UPDATE progmode1 SET assigned_spec_op = (SELECT x.assigned_spec_op FROM interim x WHERE progmode1.mach_num=x.mach_num and x.shift=1)”,“mocrosoft”)
else:
//logic to set bit to send alarm to alarm log
[quote=“AlThePal”]Hi mrtweaver,
I’m no SQL expert either but I’ll have a go. Bear in mind I only have access to MySQL so this solution may not work in SQL2005.
I’m assuming table1 and table2 both have a fixed size of 99 rows - you mention checking the status of bita in table2, so the rows must already exist. To copy the data use the following SQL:
UPDATE table2 SET
col1=(select col1 from table1 where indexnum=table2.indexnum),
col2=(select col2 from table1 where indexnum=table2.indexnum),
col3=(select col3 from table1 where indexnum=table2.indexnum),
col4=(select col4 from table1 where indexnum=table2.indexnum),
col5=(select col5 from table1 where indexnum=table2.indexnum)
where bita=0
Setting bitb to one if bita is one can be achieved with a separate statement:
UPDATE table2 SET bitb=bitaI can’t immediately see the benefit of this, as the bitb column will always end up the same as the bita column
Let us know is this helps you make progress. If anyone has a neater solution I’d love to see it.
Al[/quote]
Well I think the proper term here would be update. Because I am trying to update table2 with the data from table 1 but only update the rows where that bit is equal to 0 (zero) if that bit is a 1 (one) then do not update that row, skip it and do the next. But also when you skip it set the second bit for that row. Now that I have posted some snippets maybe that will help in clearly defining what I am trying to accomplish. Have a great day.
[quote=“nathan”]Mark,
I found that post to be confusing, particularly the part about bit-a checking and the counter.
Could you provide your code and the code that your SQL programmer helped you with in the other example?
Are you trying to INSERT or UPDATE data in table 2?
You may need to use a prepared statement to accomplish this.
Incremental Counter may be referring to an auto incrementing primary key.
You can combine SELECT and INSERT queries like this:
INSERT table2 (col1, col2, col3)
SELECT col1, col2, col3
FROM table1
WHERE bita = 1
[/quote]
Mark,
I’ll continue looking into this for you. A few things:
-
I think it can be done with 2 separate queries (or possibly a single stored procedure).
-
What are you trying to accomplish?
Martin,
I think I understand whats going on, but correct me if I’m wrong. To set the values in your table (maybe on a button press or value change in FactoryPMI), you could use the following:[code]#get the data from the tables
table1 = event.source.parent.getComponent(‘Table 1’)
table1data = fpmi.db.toPyDataSet(table1.data)
table2 = event.source.parent.getComponent(‘Table 2’)
table2data = fpmi.db.toPyDataSet(table2.data)
#cycle through the number of rows
x = 0
for table1row in table1data:
#test if bit-a = 0 in the second table
if table2data[x]["bit-a"] == 0:
#copy all the columns from table 1
fpmi.db.runUpdateQuery("UPDATE table-2 SET col1 = '%s', col2= %d WHERE ID = %d" %(table1row["col1"], table1row["col2"], table1row["ID"]))
else:
#set bit-b
fpmi.db.runUpdateQuery("UPDATE table-2 SET bit-b = 1 WHERE ID = %d" %(table1row["ID"]))[/code]
This relies on the for loop in jython that cycles through rows of a dataset (for table1row in table1data:), and assumes that there is an “ID” column that links the two tables. Of course, you will have to massage it to get the right column and table names in there, but this should work for you.
Bobby,
I think he’s trying to do this with FactorySQL. Kind of like an UPDATE/SELECT query, but with an INSERT. Any ideas on how to do that?
In FactorySQL, it would be easy to use action items to gather and update the data for a single row, but I can’t think of any way to iterate through the tables without some really crazy tricks (that probably won’t work consistently).
We need a bit more information about the process, but instead of going to all this trouble, I would suggest that you use bit-a (in an action item) as the trigger for your groups that write to table 2.
Martin, is this related to your other post about the PLC’s going down?
Sorry about messing up your name Martin. We should really discuss your requirements at a higher level. We can probably help you solve your problem elegantly.