Can somone help with this SQL query

Hi people,

I wasn't sure where to post this, it's a specific SQL syntax type problem, and didn't know where it should go. Sorry if there's a better place for it!

I want to update table 'Pupils' with some data from table 'TempAttendance'. I have an id number in the temp table that matches up with the pupil's id number in the proper 'Pupils' table.

I've tried loads, but I'm struggling!

So I want to update table Pupils.attendance with the value from TempAttendance.attendance where the ID = the ID. I can't get the syntax right. Pleas help if you can!

Thanks

update pupils set attendance = (select attendance from TempAttendance where pupils.ID = TempAttendance.ID);

May sure you get only 1 record for each pupil.ID = TempAttendance.ID.

|||

s_kushal answer is my answer.Plz check out

Thank u

Baba

Please remember to click "Mark as Answer" on this post if it helped you.

|||

thanks for the efforts guys but I worked it out before this was moderated and posted!

kushal I already tried that and it didn't work.

This is what worked:

UPDATE tbl_pupils
SET ppl_attendance =
(SELECT Attendance
FROM dataget.TempAttendance
WHERE dataget.TempAttendance.adno = tbl_pupils.ppl_adno)
WHERE EXISTS
(SELECT Attendance
FROM dataget.TempAttendance
WHERE dataget.TempAttendance.adno = tbl_pupils.ppl_adno)

Thanks for helping anyway

can somone help me tell what is wrong with this coding?

GLOBAL STRING Line1_BatchID
GLOBAL STRING Line1_CompletedDate
GLOBAL INT Line1_CompletedQty
GLOBAL STRING Line1_CompletedTime
GLOBAL STRING Line1_ExpiryDate
GLOBAL INT Line1_Qty
GLOBAL STRING Line1_OrderID

GLOBAL INT Line1_BatchID_ID
GLOBAL INT Line1_CompletedDate_ID
GLOBAL INT Line1_CompletedQty_ID
GLOBAL INT Line1_CompletedTime_ID
GLOBAL INT Line1_ExpiryDate_ID
GLOBAL INT Line1_OrderStatus_ID

GLOBAL STRING Line2_BatchID
GLOBAL STRING Line2_CompletedDate
GLOBAL INT Line2_CompletedQty
GLOBAL STRING Line2_CompletedTime
GLOBAL STRING Line2_ExpiryDate
GLOBAL STRING Line2_OrderStatus
GLOBAL INT Line2_Qty
GLOBAL STRING Line2_OrderID

GLOBAL INT Line2_BatchID_ID
GLOBAL INT Line2_CompletedDate_ID
GLOBAL INT Line2_CompletedQty_ID
GLOBAL INT Line2_CompletedTime_ID
GLOBAL INT Line2_ExpiryDate_ID
GLOBAL INT Line2_OrderStatus_ID

GLOBAL STRING Line3_BatchID
GLOBAL STRING Line3_CompletedDate
GLOBAL INT Line3_CompletedQty
GLOBAL STRING Line3_CompletedTime
GLOBAL STRING Line3_ExpiryDate
GLOBAL STRING Line3_OrderStatus
GLOBAL INT Line3_Qty
GLOBAL STRING Line3_OrderID

GLOBAL INT Line3_BatchID_ID
GLOBAL INT Line3_CompletedDate_ID
GLOBAL INT Line3_CompletedQty_ID
GLOBAL INT Line3_CompletedTime_ID
GLOBAL INT Line3_ExpiryDate_ID
GLOBAL INT Line3_OrderStatus_ID

GLOBAL STRING result
GLOBAL STRING result2

FUNCTION getOrderDetail(STRING Product_ID)
INT counter = 0
INT statusSQL, sqlResult;
STRING Sql1
STRING Sql2
STRING Sql3
result = ""

statusSQL = SQLConnect("DSN=SQLSRV_TBLS;SRVR=localhost;DB=FYPJ Integration of SAP NetweaverData;UID=labuser;PWD=success;");
IF statusSQL <> -1 THEN //If Connection Success
Sql1 = "SELECT SetId FROM ProductionDataField WHERE (Field = 'productid') AND (DataValue = '" + Product_ID + "')"
sqlResult = SQLExec(statusSQL, Sql1);
IF sqlResult = 0 THEN //If SQL Success
WHILE SQLNext(statusSQL) = 0 DO
IF result <> "" THEN
result = result + ","
END
result = result + SQLGetField(statusSQL, "SetId")
END
END

Sql2 = "SELECT SetId FROM ProductionDataField WHERE (Field = 'order status') AND (DataValue = 'pending') AND (SetId IN (" + result + ")) Order by SetID"
sqlResult = SQLExec(statusSQL, Sql2);
IF sqlResult = 0 THEN //If SQL Success
result = ""
IF SQLNext(statusSQL) = 0 THEN
//Select first SetId
result = SQLGetField(statusSQL, "SetId")
END
END

Sql3 = "SELECT Id, DataValue FROM ProductionDataField WHERE (SetId IN (" + result + ")) and (IsActive = 1) Order by field"
sqlResult = SQLExec(statusSQL, Sql3);
IF sqlResult = 0 THEN //If SQL Success
WHILE SQLNext(statusSQL) = 0 DO
IF Product_ID = "Biscuit" THEN
IF counter < 9 THEN
IF counter = 0 THEN
Line1_BatchID_ID = SQLGetField(statusSQL , "ID")
END
IF counter = 1 THEN
Line1_CompletedDate_ID = SQLGetField(statusSQL , "ID")
END
IF counter = 2 THEN
Line1_CompletedQty_ID = SQLGetField(statusSQL , "ID")
Line1_CompletedQty = SQLGetField(statusSQL , "DataValue")
END
IF counter = 3 THEN
Line1_CompletedTime_ID = SQLGetField(statusSQL , "ID")
END
IF counter = 4 THEN
Line1_ExpiryDate_ID = SQLGetField(statusSQL , "ID")
END
IF counter = 5 THEN
Line1_OrderStatus_ID = SQLGetField(statusSQL , "ID")
END
IF counter = 6 THEN
Line1_OrderID = SQLGetField(statusSQL , "DataValue")
END
IF counter = 7 THEN
END
IF counter = 8 THEN
Line1_Qty = SQLGetField(statusSQL , "DataValue")
END
END
END

IF Product_ID = "ChocolateBiscuit" THEN
IF counter = 0 THEN
Line2_BatchID_ID = SQLGetField(statusSQL , "ID")
END
IF counter = 1 THEN
Line2_CompletedDate_ID = SQLGetField(statusSQL , "ID")
END
IF counter = 2 THEN
Line2_CompletedQty_ID = SQLGetField(statusSQL , "ID")
Line2_CompletedQty = SQLGetField(statusSQL , "DataValue")
END
IF counter = 3 THEN
Line2_CompletedTime_ID = SQLGetField(statusSQL , "ID")
END
IF counter = 4 THEN
Line2_ExpiryDate_ID = SQLGetField(statusSQL , "ID")
END
IF counter = 5 THEN
Line2_OrderStatus_ID = SQLGetField(statusSQL , "ID")
END
IF counter = 6 THEN
Line2_OrderID = SQLGetField(statusSQL , "DataValue")
END
IF counter = 7 THEN

END
IF counter = 8 THEN
Line2_Qty = SQLGetField(statusSQL , "DataValue")
END
END

IF Product_ID = "PeanutButterBiscuit" THEN
IF counter = 0 THEN
Line3_BatchID_ID = SQLGetField(statusSQL , "ID")
END
IF counter = 1 THEN
Line3_CompletedDate_ID = SQLGetField(statusSQL , "ID")
END
IF counter = 2 THEN
Line3_CompletedQty_ID = SQLGetField(statusSQL , "ID")
Line3_CompletedQty = SQLGetField(statusSQL , "DataValue")
END
IF counter = 3 THEN
Line3_CompletedTime_ID = SQLGetField(statusSQL , "ID")
END
IF counter = 4 THEN
Line3_ExpiryDate_ID = SQLGetField(statusSQL , "ID")
END
IF counter = 5 THEN
Line3_OrderStatus_ID = SQLGetField(statusSQL , "ID")
END
IF counter = 6 THEN
Line3_OrderID = SQLGetField(statusSQL , "DataValue")
END
IF counter = 7 THEN

END
IF counter = 8 THEN
Line3_Qty = SQLGetField(statusSQL , "DataValue")
END
END

counter = counter + 1
END
END

END
SQLEnd(statusSQL)
SQLDisconnect("DSN=SQLSRV_TBLS")
END

FUNCTION UpdateBatchID(STRING Product_ID)
INT statusSQL, sqlResult;
STRING Sql1
statusSQL = SQLConnect("DSN=SQLSRV_TBLS;SRVR=localhost;DB=FYPJ Integration of SAP NetweaverData;UID=labuser;PWD=success;");
IF statusSQL <> -1 THEN //If Connection Success
IF Product_ID = "Biscuit" THEN
Sql1 = "UPDATE ProductionDataField SET [DataValue] = '" + Line1_BatchID + "' WHERE [Id] = " + IntToStr(Line1_BatchID_ID)
END
IF Product_ID = "ChocolateBiscuit" THEN
Sql1 = "UPDATE ProductionDataField SET [DataValue] = '" + Line2_BatchID + "' WHERE [Id] = " + IntToStr(Line2_BatchID_ID)
END
IF Product_ID = "PeanutButterBiscuit" THEN
Sql1 = "UPDATE ProductionDataField SET [DataValue] = '" + Line3_BatchID + "' WHERE [Id] = " + IntToStr(Line3_BatchID_ID)
END

sqlResult = SQLExec(statusSQL, Sql1);
END
SQLEnd(statusSQL)
SQLDisconnect("DSN=SQLSRV_TBLS")
END

FUNCTION UpdateCompletedQty(STRING Product_ID)
INT statusSQL, sqlResult;
STRING Sql1
statusSQL = SQLConnect("DSN=SQLSRV_TBLS;SRVR=localhost;DB=FYPJ Integration of SAP NetweaverData;UID=labuser;PWD=success;");
IF statusSQL <> -1 THEN //If Connection Success
IF Product_ID = "Biscuit" THEN
Sql1 = "UPDATE ProductionDataField SET DataValue = '" + IntToStr(Line1_CompletedQty) + "' WHERE [Id] = " + IntToStr(Line1_CompletedQty_ID)
END
IF Product_ID = "ChocolateBiscuit" THEN
Sql1 = "UPDATE ProductionDataField SET DataValue = '" + IntToStr(Line2_CompletedQty) + "' WHERE [Id] = " + IntToStr(Line2_CompletedQty_ID)
END
IF Product_ID = "PeanutButterBiscuit" THEN
Sql1 = "UPDATE ProductionDataField SET DataValue = '" + IntToStr(Line3_CompletedQty) + "' WHERE [Id] = " + IntToStr(Line3_CompletedQty_ID)
END

sqlResult = SQLExec(statusSQL, Sql1);
END
SQLEnd(statusSQL)
SQLDisconnect("DSN=SQLSRV_TBLS")
END

FUNCTION UpdateCompletedOrder(STRING Product_ID)
INT statusSQL, sqlResult;
STRING Sql1
statusSQL = SQLConnect("DSN=SQLSRV_TBLS;SRVR=localhost;DB=FYPJ Integration of SAP NetweaverData;UID=labuser;PWD=success;");
IF statusSQL <> -1 THEN //If Connection Success
IF Product_ID = "Biscuit" THEN
Sql1 = "UPDATE ProductionDataField SET [DataValue] = '" + Line1_CompletedDate + "' WHERE [Id] = " + IntToStr(Line1_CompletedDate_ID)
sqlResult = SQLExec(statusSQL, Sql1);

Sql1 = "UPDATE ProductionDataField SET [DataValue] = '" + Line1_CompletedTime + "' WHERE [Id] = " + IntToStr(Line1_CompletedTime_ID)
sqlResult = SQLExec(statusSQL, Sql1);

Sql1 = "UPDATE ProductionDataField SET [DataValue] = '" + Line1_ExpiryDate + "' WHERE [Id] = " + IntToStr(Line1_ExpiryDate_ID)
sqlResult = SQLExec(statusSQL, Sql1);

Sql1 = "UPDATE ProductionDataField SET [DataValue] = 'Completed' WHERE [Id] = " + IntToStr(Line1_OrderStatus_ID)
sqlResult = SQLExec(statusSQL, Sql1);
END

IF Product_ID = "ChocolateBiscuit" THEN
Sql1 = "UPDATE ProductionDataField SET [DataValue] = '" + Line2_CompletedDate + "' WHERE [Id] = " + IntToStr(Line2_CompletedDate_ID)
sqlResult = SQLExec(statusSQL, Sql1);

Sql1 = "UPDATE ProductionDataField SET [DataValue] = '" + Line2_CompletedTime + "' WHERE [Id] = " + IntToStr(Line2_CompletedTime_ID)
sqlResult = SQLExec(statusSQL, Sql1);

Sql1 = "UPDATE ProductionDataField SET [DataValue] = '" + Line2_ExpiryDate + "' WHERE [Id] = " + IntToStr(Line2_ExpiryDate_ID)
sqlResult = SQLExec(statusSQL, Sql1);

Sql1 = "UPDATE ProductionDataField SET [DataValue] = 'Completed' WHERE [Id] = " + IntToStr(Line2_OrderStatus_ID)
sqlResult = SQLExec(statusSQL, Sql1);

END

IF Product_ID = "PeanutButterBiscuit" THEN
Sql1 = "UPDATE ProductionDataField SET [DataValue] = '" + Line3_CompletedDate + "' WHERE [Id] = " + IntToStr(Line3_CompletedDate_ID)
sqlResult = SQLExec(statusSQL, Sql1);

Sql1 = "UPDATE ProductionDataField SET [DataValue] = '" + Line3_CompletedTime + "' WHERE [Id] = " + IntToStr(Line3_CompletedTime_ID)
sqlResult = SQLExec(statusSQL, Sql1);

Sql1 = "UPDATE ProductionDataField SET [DataValue] = '" + Line3_ExpiryDate + "' WHERE [Id] = " + IntToStr(Line3_ExpiryDate_ID)
sqlResult = SQLExec(statusSQL, Sql1);

Sql1 = "UPDATE ProductionDataField SET [DataValue] = 'Completed' WHERE [Id] = " + IntToStr(Line3_OrderStatus_ID)
sqlResult = SQLExec(statusSQL, Sql1);
END
END
SQLEnd(statusSQL)
SQLDisconnect("DSN=SQLSRV_TBLS")
END

INT FUNCTION getLine1_Qty()
RETURN Line1_Qty
END

INT FUNCTION getLine1_CompletedQty()
RETURN Line1_CompletedQty
END

STRING FUNCTION getLine1_OrderID()
RETURN Line1_OrderID
END

STRING FUNCTION getLine1_BatchID()
RETURN Line1_BatchID
END

INT FUNCTION getLine2_Qty()
RETURN Line2_Qty
END

INT FUNCTION getLine2_CompletedQty()
RETURN Line2_CompletedQty
END

STRING FUNCTION getLine2_OrderID()
RETURN Line2_OrderID
END

STRING FUNCTION getLine2_BatchID()
RETURN Line2_BatchID
END

INT FUNCTION getLine3_Qty()
RETURN Line3_Qty
END

INT FUNCTION getLine3_CompletedQty()
RETURN Line3_CompletedQty
END

STRING FUNCTION getLine3_OrderID()
RETURN Line3_OrderID
END

STRING FUNCTION getLine3_BatchID()
RETURN Line3_BatchID
END

FUNCTION dbTest()

INT statusSQL, sqlResult;
INT counter = 0
STRING Sql1
STRING Sql2
STRING Sql3
STRING Product_ID = "Biscuit"
result = ""

statusSQL = SQLConnect("DSN=SQLSRV_TBLS;SRVR=localhost;DB=FYPJ Integration of SAP NetweaverData;UID=labuser;PWD=success;");
IF statusSQL <> -1 THEN //If Connection Success
Sql1 = "SELECT SetId FROM ProductionDataField WHERE (Field = 'productid') AND (DataValue = '" + Product_ID + "')"
sqlResult = SQLExec(statusSQL, Sql1);
IF sqlResult = 0 THEN //If SQL Success
WHILE SQLNext(statusSQL) = 0 DO
IF result <> "" THEN
result = result + ","
END
result = result + SQLGetField(statusSQL, "SetId")
END
END

Sql2 = "SELECT SetId FROM ProductionDataField WHERE (Field = 'order status') AND (DataValue = 'pending') AND (SetId IN (" + result + "))"
sqlResult = SQLExec(statusSQL, Sql2);
IF sqlResult = 0 THEN //If SQL Success
result = ""
IF SQLNext(statusSQL) = 0 THEN
result = SQLGetField(statusSQL, "SetId")
END
END

Sql3 = "SELECT Id, DataValue FROM ProductionDataField WHERE (SetId IN (" + result + ")) and (IsActive = 1) Order by field"
sqlResult = SQLExec(statusSQL, Sql3);
IF sqlResult = 0 THEN //If SQL Success
WHILE SQLNext(statusSQL) = 0 DO
IF Product_ID = "Biscuit" THEN
IF counter = 0 THEN
Line1_BatchID_ID = SQLGetField(statusSQL , "ID")
Line1_BatchID = SQLGetField(statusSQL , "DataValue")
END
IF counter = 1 THEN
Line1_CompletedDate_ID = SQLGetField(statusSQL , "ID")
END
IF counter = 2 THEN
Line1_CompletedQty_ID = SQLGetField(statusSQL , "ID")
Line1_CompletedQty = SQLGetField(statusSQL , "DataValue")
END
IF counter = 3 THEN
Line1_CompletedTime_ID = SQLGetField(statusSQL , "ID")
END
IF counter = 4 THEN
Line1_ExpiryDate_ID = SQLGetField(statusSQL , "ID")
END
IF counter = 5 THEN
Line1_OrderStatus_ID = SQLGetField(statusSQL , "ID")
END
IF counter = 6 THEN
Line1_OrderID = SQLGetField(statusSQL , "DataValue")
END
IF counter = 7 THEN

END
IF counter = 8 THEN
Line1_Qty = SQLGetField(statusSQL , "DataValue")
END
END

IF Product_ID = "ChocolateBiscuit" THEN
IF counter = 0 THEN
Line2_BatchID_ID = SQLGetField(statusSQL , "ID")
Line2_BatchID = SQLGetField(statusSQL , "DataValue")
END
IF counter = 1 THEN
Line2_CompletedDate_ID = SQLGetField(statusSQL , "ID")
END
IF counter = 2 THEN
Line2_CompletedQty_ID = SQLGetField(statusSQL , "ID")
Line2_CompletedQty = SQLGetField(statusSQL , "DataValue")
END
IF counter = 3 THEN
Line2_CompletedTime_ID = SQLGetField(statusSQL , "ID")
END
IF counter = 4 THEN
Line2_ExpiryDate_ID = SQLGetField(statusSQL , "ID")
END
IF counter = 5 THEN
Line2_OrderStatus_ID = SQLGetField(statusSQL , "ID")
END
IF counter = 6 THEN
Line2_OrderID = SQLGetField(statusSQL , "DataValue")
END
IF counter = 7 THEN

END
IF counter = 8 THEN
Line2_Qty = SQLGetField(statusSQL , "DataValue")
END
END

IF Product_ID = "PeanutButterBiscuit" THEN
IF counter = 0 THEN
Line3_BatchID_ID = SQLGetField(statusSQL , "ID")
Line3_BatchID = SQLGetField(statusSQL , "DataValue")
END
IF counter = 1 THEN
Line3_CompletedDate_ID = SQLGetField(statusSQL , "ID")
END
IF counter = 2 THEN
Line3_CompletedQty_ID = SQLGetField(statusSQL , "ID")
Line3_CompletedQty = SQLGetField(statusSQL , "DataValue")
END
IF counter = 3 THEN
Line3_CompletedTime_ID = SQLGetField(statusSQL , "ID")
END
IF counter = 4 THEN
Line3_ExpiryDate_ID = SQLGetField(statusSQL , "ID")
END
IF counter = 5 THEN
Line3_OrderStatus_ID = SQLGetField(statusSQL , "ID")
END
IF counter = 6 THEN
Line3_OrderID = SQLGetField(statusSQL , "DataValue")
END
IF counter = 7 THEN

END
IF counter = 8 THEN
Line3_Qty = SQLGetField(statusSQL , "DataValue")
END
END

counter = counter + 1
END
END

END
SQLEnd(statusSQL)
SQLDisconnect("DSN=SQLSRV_TBLS")
END

STRING FUNCTION getresult()
RETURN result
END

STRING FUNCTION getresult2()
RETURN result2
END

Give us some clues. What do you think may be the problem?

Most of us are not going to spend much time looking through your code without a hint about what we are looking for.

And it appears to be VB5 (or earlier) code -GLOBAL variables have long fallen by the wayside.

|||i have some field in the SQL table, when information is passed in, the data goes into the wrong field. for eg, i have the field date and quantity, when information is passed into the SQL, the data got mixed up, date data is stored into the quantity field and quantity is stored in to the date field....

Can somone crack this query?

This is a simplification of a query I'm attempting to write at the
moment. I can do it with a temp table, but I think it can be done in
one query. 15 minutes of fame to the correct answer.

3 tables:
Table Continent
================Columns==================
Columns:
ID (primary key)
Name
================Data=====================
NA,North America
SA,South America

Table Country
================Columns==================
ContinentID (foreign key)
Name
Population
================Data=====================
NA,United States,295734134
NA,Canada,32805041
SA,Brazil,186112794
SA,Peru, 27269482

Can someone come up with the query to return the country with the
lowest population in each continent, ie:
CA,Canada,32805041
SA,Peru,27269482

Need to have the country name in the query (would be very easy if you
didn't!).--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1

SELECT ContinentID, Name, Population
FROM Country As C
WHERE Population = (SELECT Min(Population) FROM Country
WHERE ContinentID = C.ContinentID)
ORDER BY ContinentID, Name

Do I get a Gold Star?
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQtMcJYechKqOuFEgEQK58wCgyTJWEzJIGiiynKX9fRrHOA n2/5AAnjhF
XjBxajYEv1Afl+VAJVYY9DLI
=3lQX
--END PGP SIGNATURE--

russ wrote:
> This is a simplification of a query I'm attempting to write at the
> moment. I can do it with a temp table, but I think it can be done in
> one query. 15 minutes of fame to the correct answer.
> 3 tables:
> Table Continent
> ================Columns==================
> Columns:
> ID (primary key)
> Name
> ================Data=====================
> NA,North America
> SA,South America
> Table Country
> ================Columns==================
> ContinentID (foreign key)
> Name
> Population
> ================Data=====================
> NA,United States,295734134
> NA,Canada,32805041
> SA,Brazil,186112794
> SA,Peru, 27269482
> Can someone come up with the query to return the country with the
> lowest population in each continent, ie:
> CA,Canada,32805041
> SA,Peru,27269482
> Need to have the country name in the query (would be very easy if you
> didn't!).|||you most certainly do! correlated sub-query, ended up doing the same
myself.

thanks for your help